Mysql 需要按总金额检索前 3 个供应商，每个类别子类别

发布于11月03日

我有这个数据库表:http://sqlfiddle.com/#!9/e1dd38e/7

我正在try 查询它，这样它就会遍历每个类别，返回每个子类别的前3名供应商，按他们的总支出(sum(Amount))排序，前3名之外的供应商将被汇总为总数，并被包括为"Other"

还包括类别和子类别的总金额

我很感谢大家的帮助，在这件事上苦苦挣扎

预期结果将是:

Paper 515.54
    Napkins 314.99
        Paper LLC 110.49
        Dunder Mifflin 70.99
        Brawny 60.02
        Others 73.49
    Towels 200.55
        Acme LLC 52.01
        Michigan Paper 41.23
        Cascades LLC 40.55
        Others 66.76
Food 205.54
    Apples 178.76
        Orchards LLC 97.10
        Northstar Orchard 41.23
        Apples LLC 40.43
    Pizza 26.78
        Dominos 16.53
        Pizza Hut 10.25

推荐答案

对于新请求的需求Also including total amount for category and subcategories，您可以在上一个答案中提供的查询中使用总和窗口函数，如下所示:

WITH purchases_cat_sum AS
(
  SELECT *,
         SUM(amount) OVER (PARTITION BY category) category_total_amount,
         SUM(amount) OVER (PARTITION BY category, subcategory) subcategory_total_amount
  FROM purchases
)
SELECT category, category_total_amount,
       subcategory, subcategory_total_amount,
       CASE grp WHEN 'Others'
         THEN CONCAT(COUNT(*),' ', grp)
         ELSE grp
       END AS vendor,
       SUM(sum_amount) AS total_amount
FROM
(
  SELECT category, category_total_amount,
         subcategory, subcategory_total_amount,
         vendor, SUM(amount) sum_amount,
       CASE 
         WHEN ROW_NUMBER() OVER (PARTITION BY category, subcategory ORDER BY SUM(amount) DESC) <=3
         THEN vendor
         ELSE 'Others'
       END AS grp
  FROM purchases_cat_sum
  GROUP BY category,category_total_amount, 
           subcategory, subcategory_total_amount, 
           vendor
) T
GROUP BY category,category_total_amount, 
         subcategory, subcategory_total_amount, 
         grp
ORDER BY category DESC, subcategory, 
         CASE WHEN grp<>'Others'THEN 1 ELSE 2 END, 
         SUM(sum_amount) DESC

请看demo.