如何统计MySQL中每一列的数据？

Question

我有一些用于数据透视表的 mysql 脚本，然后计算其中的一些数据：

SELECT A.Line,
       week1.1stweek, week2.2ndweek,
       IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0) AS TOTAL
FROM inspection_report AS A
LEFT JOIN(
           SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 1stweek
           FROM inspection_report
           WHERE DAY(Inspection_datetime) BETWEEN 1 AND 7
           GROUP BY Line, WEEK(Inspection_datetime), YEAR(Inspection_datetime)
          ) AS week1 USING (Line)
LEFT JOIN(
           SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 2ndweek
           FROM inspection_report
           WHERE DAY(Inspection_datetime) BETWEEN 8 AND 14
           GROUP BY Line, WEEK(Inspection_datetime), YEAR(Inspection_datetime)
          ) AS week2 USING (Line)
GROUP BY Line

它使表头显示为按钮。 之后我想在每列下面做一个“SUM”，例如：

Line          1stweek            2ndweek             total
1               12                 2                   14
2               3                  0                    3
SUM             15                 2                   17

我该怎么做？

Answer 1

这让我很感兴趣，因此我在此处的在线 SQL 沙箱中实现了等效项。

选择 ifnull(toy, "SUM") 作为玩具，sum(s1.store1) 作为 store2，sum(s2.store2) 作为 store1，sum(ifnull(store1,0)+ifnull(store2,0)) as Total from (select toy_id, sum(inventory) as store1 from store_inventory where store_id=1 group by toy_id) as s1 left join (select toy_id, sum(inventory) as store2 from store_inventory where store_id=2 group by toy_id) as s2 using ( toy_id) left join toy_info using (toy_id) group by toy with rollup;

（请原谅我缺乏 Markdown 技能，这是我的第一篇文章。）

Answer 2

使用 WITH ROLLUP 修饰符。

http://dev.mysql.com/doc/ refman/5.0/en/group-by-modifiers.html

您必须修改外部 select 语句才能

SELECT A.Line,
       sum(week1.1stweek) as 1stweek, sum(week2.2ndweek) 2ndweek,
       sum(IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0)) AS TOTAL

使修饰符生效。如果您需要将其标记为这样，请将 A.Line 更改为 IFNULL(A.Line, "SUM") as Line。