mysql 分组取第一条数据(求分组前2位数据)
mysql 分组取第一条数据(求分组前2位数据)ORDER BY a.ModelId a.MSRP DESC每个modelId都分组且不超过2个。成功,在统计中应用很广
用Mysql分组求前2位数据。之前有用Pandas实现过,但是用sql就没那么简便了,也没那么直观。那么如何去实现呢?
核心思想:表的自连接,利用错位,having继续查询
SELECT a.ModelId a.MSRP FROM ref_target.t10_basictrim a ref_target.t10_basictrim b
WHERE a.ModelId=b.ModelId and a.MSRP<=b.msrp GROUP BY a.ModelId a.MSRP HAVING count(a.MSRP)<3
ORDER BY a.ModelId a.MSRP DESC
每个modelId都分组且不超过2个。成功,在统计中应用很广