表A
A B
-----------------
004 A001
002 A005
005 A002
006 A002
002 A002表B
A B C
-----------------
$ A003 我X
$ A004 我XX
$ A005 我XXX
002 A002 我XXXX
003 A002 我XXXXX结果
A B C
------------------
004 A001 null
002 A005 我XX
005 A002 null
006 A002 null
002 A002 我XXXX说明:
1.表A和表B连接,要返回表A所有列,所有数据
2.连接条件是B字段,若表B的A字段不是$,则加上连接条件A
3.表B的A,B是唯一的,也不会有这种情况$ A003和001 A003存在
A B
-----------------
004 A001
002 A005
005 A002
006 A002
002 A002表B
A B C
-----------------
$ A003 我X
$ A004 我XX
$ A005 我XXX
002 A002 我XXXX
003 A002 我XXXXX结果
A B C
------------------
004 A001 null
002 A005 我XX
005 A002 null
006 A002 null
002 A002 我XXXX说明:
1.表A和表B连接,要返回表A所有列,所有数据
2.连接条件是B字段,若表B的A字段不是$,则加上连接条件A
3.表B的A,B是唯一的,也不会有这种情况$ A003和001 A003存在
if OBJECT_ID('ta') is not null
drop table ta
go
create table ta(A VARCHAR(3),B VARCHAR(4))
insert into ta
select '004', 'A001' union all
select '002', 'A005' union all
select '005', 'A002' union all
select '006', 'A002' union all
select '002', 'A002'
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(A VARCHAR(3),B VARCHAR(4),C VARCHAR(10))
insert into tb
select '$', 'A003', '我X' union all
select '$', 'A004', '我XX' union all
select '$', 'A005', '我XXX' union all
select '002', 'A002', '我XXXX' union all
select '003', 'A002', '我XXXXX'
select A,B,MAX(C) C
from
(
select a.A,a.B,b.C
from ta a left join tb b on a.A=b.A and b.A<>'$' and a.B=b.B
union
select a.A,a.B,b.C
from ta a left join tb b on b.A='$' and a.B=b.B
) a
GROUP BY A,B
002 A005 我XX
改为
002 A005 我XXX
insert into @A
select '004', 'A001' union all
select '002', 'A005' union all
select '005', 'A002' union all
select '006', 'A002' union all
select '002', 'A002'declare @B table(A varchar(20),B varchar(20),C varchar(20))
insert into @B
select '$', 'A003', '我X' union all
select '$', 'A004', '我XX' union all
select '$', 'A005', '我XXX' union all
select '002', 'A002', '我XXXX' union all
select '003', 'A002', '我XXXXX'select A.*,Max(case when (B.A='$') then B.C when (B.A<>'$' and A.A=B.A) then B.C else null end) C from @A A left join @B B on A.B=B.B group by A.A,A.B(5 個資料列受到影響)
A B C
------------------
004 A001 null
002 A002 我XXXX
005 A002 null
006 A002 null
002 A005 我XXX