如题，有表table，有三个字段分别为aa,bb,cc.
表数据如下：
aa bb cc
65443 2012-04-11 40
65443 2012-04-12 50
65443 2012-04-21 250
65446 2012-04-29 130
65447 2012-04-17 test
65447 2012-04-29 1290
65448 2012-04-29 650
65449 2012-04-28 1100
65449 2012-04-29 190
现在需要查出如下结果：
65443 2012-04-21 250
65446 2012-04-29 130
65447 2012-04-29 1290
65448 2012-04-29 650
65449 2012-04-29 190
先对aa进行group by，然后取出分组后bb字段日期最大的行。
急，求教！！！
------解决方案--------------------
select * from table1 as a where not exists(select 1 from table1 where aa=a.aa and bb>a.bb)
------解决方案--------------------
select * from 表名 as a where bb=(select max(bb) from 表名 where aa=a.aa group by aa order by aa)
------解决方案--------------------
select * from 表名 as a where bb in (select max(bb) from 表名 group by aa) 用这个
------解决方案--------------------
- SQL code
create table #tb(aa int,bb datetime,cc varchar(50))insert into #tb values(65443, '2012-04-11' ,'40'), (65443, '2012-04-12' ,'50') , (65443 ,'2012-04-21' ,'250') , (65446 ,'2012-04-29' ,'130') ,(65447 ,'2012-04-17' ,'test') ,(65447 ,'2012-04-29' ,'1290'),(65448 ,'2012-04-29' ,'650'),(65449 ,'2012-04-28' ,'1100'),(65449 ,'2012-04-29' ,'190');select aa,bb,cc from(select *,row_number()over(partition by aa order by bb desc) as rin from #tb)finwhere fin.rin=1drop table #tb/*(9 row(s) affected)aa bb cc----------- ----------------------- --------------------------------------------------65443 2012-04-21 00:00:00.000 25065446 2012-04-29 00:00:00.000 13065447 2012-04-29 00:00:00.000 129065448 2012-04-29 00:00:00.000 65065449 2012-04-29 00:00:00.000 190(5 row(s) affected)*/
------解决方案--------------------
- SQL code
create table tb(aa int,bb datetime,cc varchar(50))insert into tb values(65443, '2012-04-11' ,'40'), (65443, '2012-04-12' ,'50') , (65443 ,'2012-04-21' ,'250') , (65446 ,'2012-04-29' ,'130') ,(65447 ,'2012-04-17' ,'test') ,(65447 ,'2012-04-29' ,'1290'),(65448 ,'2012-04-29' ,'650'),(65449 ,'2012-04-28' ,'1100'),(65449 ,'2012-04-29' ,'190');select * from tb as a where not exists (select 1 from tb where aa = a.aa and bb > a.bb)drop table tb
------解决方案--------------------
select a.aa,a.bb,b.cc from ( select aa,MAX(bb) as bb from #tb group by aa) a join #tb b on a.aa=b.aa and a.bb=b.bb