如果只有2种情况就很简单---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2014-12-02 13:37:13 -- Version: -- Microsoft SQL Server 2014 - 12.0.2254.0 (X64) -- Jul 25 2014 18:52:51 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.4 <X64> (Build 9860: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([id] int,[da] varchar(2),[df] sql_variant) insert [A] select 1,'AB',null union all select 2,'AC',null union all select 3,'B',null --> 测试数据:[B] if object_id('[B]') is not null drop table [B] go create table [B]([id] int,[ckda] varchar(4),[fz] int) insert [B] select 1,'AB',5 union all select 2,'ABC',5 union all select 3,'ABCD',5 --------------开始查询-------------------------- select a.id,a.da,5 as fz from [A] a inner join [B] b on a.id=b.id where a.da=b.ckda union all select a.id,a.da,2 as fz from [A] a inner join [B] b on a.id=b.id where a.da<>b.ckda ----------------结果---------------------------- /* id da fz ----------- ---- ----------- 1 AB 5 2 AC 2 3 B 2*/
比如说:如果ckda是ABC,da为AD为0分。
SELECT A.id,A.da ,CASE WHEN A.da=B.ckda THEN B.fz WHEN A.da LIKE '%[^'+B.ckda+']%' THEN 0 ELSE 2 END df FROM 表1 A JOIN 表2 B ON A.id=B.id
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-12-02 13:37:13
-- Version:
-- Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
-- Jul 25 2014 18:52:51
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.4 <X64> (Build 9860: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[da] varchar(2),[df] sql_variant)
insert [A]
select 1,'AB',null union all
select 2,'AC',null union all
select 3,'B',null
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[ckda] varchar(4),[fz] int)
insert [B]
select 1,'AB',5 union all
select 2,'ABC',5 union all
select 3,'ABCD',5
--------------开始查询--------------------------
select a.id,a.da,5 as fz
from [A] a inner join [B] b on a.id=b.id
where a.da=b.ckda
union all
select a.id,a.da,2 as fz
from [A] a inner join [B] b on a.id=b.id
where a.da<>b.ckda
----------------结果----------------------------
/*
id da fz
----------- ---- -----------
1 AB 5
2 AC 2
3 B 2*/
A.id,A.da
,CASE WHEN A.da=B.ckda THEN B.fz
WHEN A.da LIKE '%[^'+B.ckda+']%' THEN 0
ELSE 2 END df
FROM 表1 A JOIN 表2 B ON A.id=B.id