mysql - SQL SUM 每个类别的前 5 个值，然后每个 ID 的 SUM 类别总数

Question

我正在尝试生成一个 SQL 语句，以对每个用户 ID 的类别中的前 5 个值求和，以创建总计。这可能吗？我该如何实现？我可以对每个类别或所有类别的前 5 个进行求和，但很难了解如何将每个单独类别的总数求和。

例如，

ID           Cater   Weight
--------------------------------
1            Cheese    10
2            Bacon     15
1            Cheese    5
2            Bacon     10
1            Cheese    22
2            Cheese    5
1            Bacon     10
1            Cheese    10
2            Cheese    5
1            Cheese    20
2            Bacon     10
1            Cheese    30

我要找的结果是，

ID      Total_Weight
-------------------
1            102    Top 5 Cheese (10+22+10+20+30) + Top 5 Bacon (10)
2            45     Top 5 Cheese (5+5) + Top 5 Bacon (15+10+10)

前 5 名之外的任何值都将被忽略。

下面的代码显示所有类别中前 5 个权重的总和作为总权重。我可以从一个语句中实现我想要的吗？

$log = "SELECT id, cater,
    SUM(weight) AS total_weight
   FROM   (   SELECT id,
                 CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1
                      ELSE @ROW_NUMBER := 1
                  END AS rn,
                 cater,
                 weight,
                 @id := id
            FROM individual,
                 (SELECT @ROW_NUMBER := 1, @ID := '') r
           ORDER
              BY
                 id, weight DESC
      ) TMP
 WHERE rn <= 5
   AND cater <> ''

   GROUP
BY id
 ORDER
BY total_weight DESC";

Answer 1

可能还有其他更好的解决方案。但这将提供您预期的结果-

SELECT B.id, 
SUM(T_weight) Total_Weight,
group_concat(concat('TOP 5 ',B.cater,' (',B.T,')') SEPARATOR ' ') Details
FROM
(
    SELECT ID,cater,SUM(Weight) T_weight,group_concat(weight SEPARATOR '+') T
    FROM 
    (    
        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cheese' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Bacon' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat3' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat4' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat5' ORDER BY id, weight DESC
        )A WHERE rn < 6


        UNION ALL

        SELECT *    FROM
        (
            SELECT id,cater, CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1 ELSE @ROW_NUMBER := 1 END AS rn,weight,@id := id
            FROM your_table,(SELECT @ROW_NUMBER := 1, @ID := ''
        ) r
        WHERE cater = 'Cat6' ORDER BY id, weight DESC
        )A WHERE rn < 6
    )A
    GROUP BY ID,Cater
)B
group by id

输出是-

1   191 TOP 5 Cheese (10+22+20+10+30) TOP 5 Cat3 (25+9+20+16+13) TOP 5 Bacon (10)
2   45  TOP 5 Cheese (5+5) TOP 5 Bacon (15+10+10)