表1:t1
字段:id,f1,f2表2:t2
字段:id,h1,h2表1与表2通过字段id关联,是1对多的关系
例如:表1中有信息:
id,f1,f2
1,111,222
2,333,444表2中有信息:
id,h1,h2
1,aaa,2006-03-01
1,bbb,2006-03-05
1,ccc,2006-02-11要求搜索结果:(表1中所有字段,以及表2中最新h1字段的记录)
id,f1,f2,h1,h2
字段:id,f1,f2表2:t2
字段:id,h1,h2表1与表2通过字段id关联,是1对多的关系
例如:表1中有信息:
id,f1,f2
1,111,222
2,333,444表2中有信息:
id,h1,h2
1,aaa,2006-03-01
1,bbb,2006-03-05
1,ccc,2006-02-11要求搜索结果:(表1中所有字段,以及表2中最新h1字段的记录)
id,f1,f2,h1,h2
from 表1 a left join 表2 b
on a.id=b.id and not exists
(select 1 from 表2 where id=b.id and h2>b.h2)
where t1.id =t2.id
and t2.h2 = (select max(h2) from t2 where t2.id = t1.id)
insert @b1
select 1,'111','222' union
select 2,'333','444'declare @b2 table(id int,h1 varchar(10),h2 datetime)
insert @b2
select 1,'aaa','2006-03-01' union
select 1,'bbb','2006-03-05' union
select 1,'ccc','2006-02-11'select a.*,b.h1,c.h2
from @b1 a,@b2 b,
(select id,max(h2) h2 from @b2 group by id) c
where a.id = c.id
and b.id = c.id
and b.h2 = c.h2