有一表基本如下:
id(char) begindate(date) pay(mony)
23 2002-10-1 450
23 2003-5-1 500
23 2004-3-1 600
96 2002-10-1 550
96 2003-5-1 650
要求,取出每一ID所对应的最后日期,的那条数年据,即取出如下结果:
id begindate pay
23 2004-3-1 600
96 2003-5-1 650
id(char) begindate(date) pay(mony)
23 2002-10-1 450
23 2003-5-1 500
23 2004-3-1 600
96 2002-10-1 550
96 2003-5-1 650
要求,取出每一ID所对应的最后日期,的那条数年据,即取出如下结果:
id begindate pay
23 2004-3-1 600
96 2003-5-1 650
create table table1( d char(2),begindate datetime,pay money )
--加入测试数据
insert into table1 values(23,cast('2002-10-1' as datetime) ,450)
insert into table1 values(23,cast('2003-5-1' as datetime),500)
insert into table1 values(23,cast('2004-3-1' as datetime),600)
insert into table1 values(96,cast('2002-10-1' as datetime),550)
insert into table1 values(96,cast('2003-5-1' as datetime),650)SQL语句:
select A.* from table1 A where begindate=(select max(begindate) from table1 B where B.d=A.d)
结果集如下:id begindate pay
96 2003-05-01 00:00:00.000 650.0000
23 2004-03-01 00:00:00.000 600.0000
from tbl A
where A.begindate in (select max(B.begindate)
from tbl B
group by year(B.begindate))