个人认为是个难题！

[ilovewebtime]

表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）

[SQLCenter]

select a.*, b.img from tbA a left join tbB b on a.id = b.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

[ilovewebtime]

感觉效率都不高呀，还有其它办法吗？

[duanzhi1984]

[Quote=引用 4 楼 ilovewebtime 的回复:]
感觉效率都不高呀，还有其它办法吗？
[/Quote]

哥们以上的效率都不错了。。。

[xman_78tom]

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;

[昵称被占用了]

select id,uid
  ,(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 id,uid
  ,(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