17,380
社区成员
发帖
与我相关
我的任务
分享新人求助，简单的问题，group By
表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
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
...全文
请发表友善的回复…
发表回复
dtaskwhy 2017-08-14
- 打赏
- 举报
qyz 2017-08-13
- 打赏
- 举报
select * from a a1 where not exists (select 1 from a where memberId = a1.memberid and lid = a1.lid and id > a1.id);
mayanzs 2017-08-10
- 打赏
- 举报
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) 可能会出现重复
shpery 2017-08-10
- 打赏
- 举报
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