表数据如下:
content id createdate
---------------------------------------------
aaa 1 2010-01-03
bbb 1 2010-01-02
ccc 1 2010-01-05
ddd 2 2010-01-08
eee 2 2010-01-07
fff 2 2010-01-06现在我想把ID相同,createdate最大的一条记录取出来,结果应该是这样:
content id createdate
---------------------------------------------
ccc 1 2010-01-05
ddd 2 2010-01-08
我写了一条这样的SQL:
select * from t where t.createdate in(select max(createdate) from t group by id)
但这样写效率很低,是否有更好的办法?
content id createdate
---------------------------------------------
aaa 1 2010-01-03
bbb 1 2010-01-02
ccc 1 2010-01-05
ddd 2 2010-01-08
eee 2 2010-01-07
fff 2 2010-01-06现在我想把ID相同,createdate最大的一条记录取出来,结果应该是这样:
content id createdate
---------------------------------------------
ccc 1 2010-01-05
ddd 2 2010-01-08
我写了一条这样的SQL:
select * from t where t.createdate in(select max(createdate) from t group by id)
但这样写效率很低,是否有更好的办法?
from t t1
where exists (select 1 from t t2
where t2.id=t1.id
group by t2.id
having max(t2.createdate)=t1.createdate );
WHERE T1.ID=T2.ID
AND T1.CREATEDATE=T2.CREATEDATE我测试了下,好像比你那个还快一点,有更好的不?
create index t_inx on t(id,createdate);-- 查询的时候如果不自动走索引的话,强制指定此索引
from(select content, id, createdate,row_number() over (partition by id order by createdate desc) cnt from t) where cnt=1;使用分析函数应该比较快。
楼主这句话有问题,in 里面选择出的日期和当前行的ID及日期匹配不对
改成
select * from t where t.createdate = (select max(t1.createdate) from t1 where t1.id=t.id) 会好些
from tb a where not exists(select 1 from tb b where a.id=b.id and a.createdate<b.createdate)
where n =1
这个就可以