22,302
社区成员




;with ta(ID,col) as
(
select 1,'A1'
union all select 2,'A2'
union all select 3,'A3'
union all select 4,'A4'
union all select 5,'A5'
union all select 6,'A6'
union all select 7,'A7'
),
tb(ID,col) as
(
select 1,'A1'
union all select 2,'A2'
union all select 3,'A3'
union all select 4,'A4'
union all select 5,'A5'
union all select 6,'A6'
union all select 7,'B7'
union all select 8,'B8'
)
select b.ID,b.col as colB, colA=case when a.col IS null then (select top 1 col from ta order by col desc) else a.col end
from tb b
left join ta a on a.col=b.col
/*
ID colB colA
1 A1 A1
2 A2 A2
3 A3 A3
4 A4 A4
5 A5 A5
6 A6 A6
7 B7 A7
8 B8 A7
*/
select *
from ta
left join tb on ta.a=tb.b or ((tb,a='B7' or tb.a='B8') and ta.a='A7')