declare @t1 table(id int,cname varchar(20)) insert into @t1 select 1,'中国' union all select 2,'美国' union all select 3,'加拿大 'declare @t2 table(id int,name varchar(20),cid int,sj datetime) insert into @t2 select 1,'北京',1,'2008-07-31 00:00:00' union all select 2,'上海',1,'2008-08-01 00:00:00' union all select 3,'纽约',2,'2008-07-15 00:00:00 ' union all select 4,'旧金山',2,'2008-07-01 00:00:00' union all select 5,'温哥华',3,'2008-08-01 00:00:00' select d.cname,b.[name] from (select c.cid,max(c.sj) as sj from @t2 c group by c.cid ) a left join @t2 b on a.cid=b.id left join @t1 d on b.id=d.id cname name -------------------- -------------------- 中国 北京 美国 上海 加拿大 纽约(3 行受影响)
declare @t1 table(id int,cname varchar(20)) insert into @t1 select 1,'中国' union all select 2,'美国' union all select 3,'加拿大 'declare @t2 table(id int,name varchar(20),cid int,sj datetime) insert into @t2 select 1,'北京',1,'2008-07-31 00:00:00' union all select 2,'上海',1,'2008-08-01 00:00:00' union all select 3,'纽约',2,'2008-07-15 00:00:00 ' union all select 4,'旧金山',2,'2008-07-01 00:00:00' union all select 5,'温哥华',3,'2008-08-01 00:00:00'select d.cname,b.[name] from (select c.cid,max(c.sj) as sj from @t2 c group by cid ) a,@t2 b,@t1 d where a.cid=b.cid and b.cid=d.id and b.sj=a.sj
insert into @t1
select 1,'中国' union all
select 2,'美国' union all
select 3,'加拿大 'declare @t2 table(id int,name varchar(20),cid int,sj datetime)
insert into @t2
select 1,'北京',1,'2008-07-31 00:00:00' union all
select 2,'上海',1,'2008-08-01 00:00:00' union all
select 3,'纽约',2,'2008-07-15 00:00:00 ' union all
select 4,'旧金山',2,'2008-07-01 00:00:00' union all
select 5,'温哥华',3,'2008-08-01 00:00:00'
select d.cname,b.[name] from
(select c.cid,max(c.sj) as sj from @t2 c group by c.cid ) a
left join @t2 b
on a.cid=b.id
left join @t1 d
on b.id=d.id
cname name
-------------------- --------------------
中国 北京
美国 上海
加拿大 纽约(3 行受影响)
insert into @t1
select 1,'中国' union all
select 2,'美国' union all
select 3,'加拿大 'declare @t2 table(id int,name varchar(20),cid int,sj datetime)
insert into @t2
select 1,'北京',1,'2008-07-31 00:00:00' union all
select 2,'上海',1,'2008-08-01 00:00:00' union all
select 3,'纽约',2,'2008-07-15 00:00:00 ' union all
select 4,'旧金山',2,'2008-07-01 00:00:00' union all
select 5,'温哥华',3,'2008-08-01 00:00:00'select d.cname,b.[name] from
(select c.cid,max(c.sj) as sj from @t2 c group by cid ) a,@t2 b,@t1 d
where a.cid=b.cid and b.cid=d.id and b.sj=a.sj
-------------------- --------------------
中国 上海
美国 纽约
加拿大 温哥华(3 行受影响)