表A:
id uid
1 1000
2 1001
3 1001
4 1002表B:
id uid img
1 1000 img1
1 0 img2
1 1005 img3
2 1003 img4
2 0 img5如何得到以下这样的查询结果id uid img
1 1000 img1
2 1001 img5
3 1001 null
4 1002 null目的说明:以A表的id为主查B表中相应的img,关键问题是如何能让uid按以下优先顺序获取,并只取其1(1:A.uid=B.uid 2:B.uid=0)
id uid
1 1000
2 1001
3 1001
4 1002表B:
id uid img
1 1000 img1
1 0 img2
1 1005 img3
2 1003 img4
2 0 img5如何得到以下这样的查询结果id uid img
1 1000 img1
2 1001 img5
3 1001 null
4 1002 null目的说明:以A表的id为主查B表中相应的img,关键问题是如何能让uid按以下优先顺序获取,并只取其1(1:A.uid=B.uid 2:B.uid=0)
,(select top 1 img from b where b.id = a.id order by case when b.uid = a.uid then 0 else 1 end,case when b.uid = 0 then 0 else 1 end,b.uid)
from a
,(select top 1 img from b where b.id = a.id order by case when b.uid = a.uid then 0 else 1 end,case when b.uid = 0 then 0 else 1 end,b.uid) as img
from a
order by id
and b.uid = case when exists (select 1 from tbB where id=a.id and uid=a.uid) then a.uid else 0 end
declare @a table(id int, uid int);
insert into @a
select 1,1000 union all select 2,1001 union all
select 3,1001 union all select 4,1002;declare @b table(id int,uid int,img varchar(10));
insert into @b
select 1,1000,'img1' union all select 1,0,'img2' union all
select 1,1005,'img3' union all select 2,1003,'img4' union all
select 2,0,'img5';
select id,uid,
(select top 1 img from @b where id=a.id and (uid=a.uid or uid=0) order by uid desc)
from @a a;select a.id,a.uid,isnull(b1.img,b2.img)
from @a a left join @b b1
on a.id=b1.id and a.uid=b1.uid
left join @b b2
on a.id=b2.id and b2.uid=0;