declare @A table(aid int,name varchar(10)) insert into @A select 1,'张三' insert into @A select 2,'李四' insert into @A select 3,'王五' insert into @A select 4,'赵六' insert into @A select 5,'田七' declare @B table(id int,aid int,ordernumb varchar(12)) insert into @B select 1,1,'a20060405001' insert into @B select 2,3,'a20060405003' insert into @B select 3,4,'a20060406001' insert into @B select 4,2,'a20060406002' insert into @B select 5,2,'a20060505001' insert into @B select 6,3,'a20060506001' insert into @B select 7,2,'a20060507001'select a.*,b.ordernumb from @A a left join @B b on a.aid=b.aid where not exists(select 1 from @B where aid=a.aid and id<b.id)/* aid name ordernumb ----------- ---------- ------------ 1 张三 a20060405001 2 李四 a20060406002 3 王五 a20060405003 4 赵六 a20060406001 5 田七 NULL */
aid name
1 张三
2 李四
3 王五
4 赵六
5 田七 表B
id aid ordernumb
1 1 a20060405001
2 3 a20060405003
3 4 a20060406001
4 2 a20060406002
5 2 a20060505001
6 3 a20060506001
7 2 a20060507001希望得到结果集:
aid name ordernumb
1 张三 a20060405001
2 李四 a20060406002
3 王五 a20060405003
4 赵六 a20060406001
5 田七 null
1 张三 a20060405001
2 李四 a20060406002
2 李四 a20060505001
2 李四 a20060507001
3 王五 a20060405003
3 王五 a20060506001
4 赵六 a20060406001
5 田七 null
insert into @A select 1,'张三'
insert into @A select 2,'李四'
insert into @A select 3,'王五'
insert into @A select 4,'赵六'
insert into @A select 5,'田七' declare @B table(id int,aid int,ordernumb varchar(12))
insert into @B select 1,1,'a20060405001'
insert into @B select 2,3,'a20060405003'
insert into @B select 3,4,'a20060406001'
insert into @B select 4,2,'a20060406002'
insert into @B select 5,2,'a20060505001'
insert into @B select 6,3,'a20060506001'
insert into @B select 7,2,'a20060507001'select
a.*,b.ordernumb
from
@A a
left join
@B b
on
a.aid=b.aid
where
not exists(select 1 from @B where aid=a.aid and id<b.id)/*
aid name ordernumb
----------- ---------- ------------
1 张三 a20060405001
2 李四 a20060406002
3 王五 a20060405003
4 赵六 a20060406001
5 田七 NULL
*/