表A
id memberId lid createTime
1 111 22 20170810
2 222 33 20170809
3 111 22 20170811
期望查出的結果：
memberId 和 lid分組之后最新的那一條，即：
id memberId lid createTime
2 222 33 20170809
3 111 22 20170811
uj5u.com熱心網友回復：
with t as (
select '1' id,'111' memberId,'22' lid,'20170810' createTime from dual
union all
select '2','222','33','20170809' from dual
union all
select '3','111','22','20170811' from dual
)
select * from t
where (memberId, lid, createTime) in
(select memberId, lid, max(createTime) from t group by memberId, lid)
order by id asc
uj5u.com熱心網友回復：
select memberId, lid, createTime from(select memberId, lid, createTime,row_number() over (partition by memberId, lid order by createTime desc) rw from A)
where rw=1;
max(createTime) 可能會出現重復
uj5u.com熱心網友回復：
select * from a a1 where not exists (select 1 from a where memberId = a1.memberid and lid = a1.lid and id > a1.id);uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/86459.html
標籤：基礎和管理
上一篇：求大神寫個Oracle的根據表名查元資料的SQL,要求形式如下，下面是sqlserver查出來的形式
下一篇：大神才會的