提取表中最近的按照某个类型各两条，谢谢

[chilli6519]

在表A中有字段 dtype 和 cdate， 如何获取按照cdate 逆序的数据， 但 字段dtype 中每个类型都取 2 条
比如A 中数据

'a','2016-3-1'
'a','2016-8-9'
'a','2015-7-3'
'a','2016-5-7'
'b','2013-3-4'
'b','2013-4-4'
'b','2013-3-4'
'b','2013-6-4'
'b','2013-5-4'
'c','2013-3-4'
'c','2013-6-4'
'c','2013-5-4'

我希望得到：

'a','2016-8-9'
'a','2015-7-3'
'b','2013-6-4'
'b','2013-5-4'
'c','2013-6-4'
'c','2013-5-4'

[weixin_42180512]

SELECT t3.dtype,max(t3.cdate)cdate from a0310 t3
WHERE t3.id not in 
(
SELECT t2.id from a0310 t2
left JOIN 
(
SELECT a.dtype,max(a.cdate)cdate from a0310 a
group by a.dtype
)t1 on t1.dtype=t2.dtype
WHERE t2.cdate=t1.cdate
)
group by t3.dtype
union all
SELECT b.dtype,max(b.cdate)cdate from a0310 b
group by b.dtype
order by dtype,cdate desc

[chengangcsdn]

如果是8.0以上就很好写 select *from ( select dtype , cdate, row_number() over(PARTITION by dtype order by cdate desc ) as rowno from a ) as t where t.rowno <= 2

[chilli6519]

引用 1 楼 chengangcsdn 的回复:

如果是8.0以上就很好写 select *from ( select dtype , cdate, row_number() over(PARTITION by dtype order by cdate desc ) as rowno from a ) as t where t.rowno <= 2

5.7版本

[chilli6519]

抱歉没看懂这个意思，如果要 3 条或者 1 条怎么调整？

引用 2 楼 weixin_42180512 的回复:

SELECT t3.dtype,max(t3.cdate)cdate from a0310 t3
WHERE t3.id not in 
(
SELECT t2.id from a0310 t2
left JOIN 
(
SELECT a.dtype,max(a.cdate)cdate from a0310 a
group by a.dtype
)t1 on t1.dtype=t2.dtype
WHERE t2.cdate=t1.cdate
)
group by t3.dtype
union all
SELECT b.dtype,max(b.cdate)cdate from a0310 b
group by b.dtype
order by dtype,cdate desc