MySQL 先按某字段分组，再取每组中前N条记录

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

按 gpcode每组 取每组 f4 最大的那条记录：

方法一：

select * from calcgsdataflash a where gscode = 'LS_F' and ymd >= 20171117 and ymd <= 20171117

and 1 >= (select count(*) from calcgsdataflash b where gscode = 'LS_F' and ymd >= 20171117 and ymd <= 20171117

and a.gpcode = b.gpcode and a.f4 <= b.f4);

前 N 条 就是 N >=

方法二：

SELECT * FROM (SELECT * FROM calcgsdataflash where gscode = 'LS_F' and ymd >= 20171117 and ymd <= 20171117) a ,

(SELECT gpcode ,MAX(f4) as f4 FROM calcgsdataflash where gscode = 'LS_F' and ymd >= 20171117 and ymd <= 20171117 GROUP BY gpcode) b

where a.gpcode = b.gpcode AND a.f4 = b.f4;