求一sql，关于left join

[kth007]

表A
id title uid
1 A1 88
2 A2 88
3 A3 66
4 A4 88
表B
id sortid uid name photo fm
1 1 88 B1 1.jpg 0
2 1 88 B2 2.jpg 0
3 2 88 B3 3.jpg 0
4 1 88 B4 4.jpg 0
5 2 88 B5 5.jpg 0
6 1 88 B6 6.jpg 1
7 1 88 B7 7.jpg 0
8 2 88 B8 8.jpg 0
9 1 88 B9 9.jpg 0
10 2 88 B10 10.jpg 0


查询表A 根据A.id=B.sortid 取表B的一条记录 根据 fm 的大小返回在根据ID大小返回
如果表B的居然不存在 也返回表A 。

比如返回
id title uid id sortid uid name photo fm
1 A1 88 6 1 88 B6 6.jpg 1
2 A2 88 10 2 88 B10 10.jpg 0
4 A4 88 null null null null null null

[guoli0813]

select A.id,A.title,A.uid,B.id as iid,B.sortid, B.uid as uuid, B.name, B.photo, B.fm
from A left join B on A.id=B.sortid
order by B.fm,A.id


小猪，给分吧，哥哥收下了，呵呵。

[jackdalsen]

select A.* ,B.* from A left join B on A.id=B.sortid order by B.fm, A.id

[Liyingyue_FFS]

select * from A left outer join B on A.id=B.sortid order by B.fm, A.id

[ydage]

select * from A left join B on A.id = B.sortid order by B.fm desc, A.id

[kth007]

都会返回 表B 的所有值

我只需要返回表B 的一条值！！！

[octwind]

select * from A left outer join B t on A.id=t.sortid 
and t.id in (select top 1 id from B where sortid=t.sortid order by B.fm desc,B.id desc)
order by  A.id

[ChinaJiaBing]

if object_id('dbo.表A') is not null
           drop table 表A
if object_id('dbo.表B') is not null
           drop table 表B 
create table 表A 
( id int,
  title varchar(7),
  uid int
)
create table 表B
( id int,
  sortid int,
  uid int,
 name varchar(7),
 photo varchar(10),
 fm int
)
go 
insert into 表A select 1,'A1',88
    union all   select 2,'A2',88
    union all   select 3,'A3',66
    union all   select 4,'A4',88
go
insert into 表B select 1,1,88,'B1','1.jpg',0
      union all select 2,1,88,'B2','2.jpg',0
      union all select 3,2,88,'B3','3.jpg',0
      union all select 4,1,88,'B4','4.jpg',0
      union all select 5,2,88,'B5','5.jpg',0
      union all select 6,1,88,'B6','6.jpg',1
      union all select 7,1,88,'B7','7.jpg',0
      union all select 8,2,88,'B8','8.jpg',0
      union all select 9,1,88,'B9','9.jpg',0
      union all select 10,2,88,'B10','10.jpg',0
GO
select a.id,a.title,a.uid,b.id,b.uid,b.name,b.photo,b.fm from 表A a
left join 表B b on a.id = b.sortid
order by b.fm desc ,b.sortid desc