记录MySQL获取每组汇总数据中按需求字段获取TOP排名数据
select * from(
select
bb.*
,(@i := CASE WHEN @name = bb.name THEN @i + 1 ELSE 1 END ) AS rownum /*先对每组赋值 组排序1*/
,(@name := bb.name) /*对每组的组排序 +1*/
from (
select
a.name
,a.product_name
,sum(a.amount) as total_amount
from
(
SELECT
`b`.`Name` /*客户分类*/
,hp.`Product_Name`
,od.`Amount`
FROM
test_table /*自己的关联表*/
) a
group by a.`name`,a.product_name
) bb
,( SELECT @i:=0, @Name:='', @productname:='' ) AS v
ORDER BY bb.Name, bb.total_amount desc
) cc where cc.rownum <= 3 /*获取前几*/