MySQL大行和列
MySQL大行和列
提问于 2017-08-02 13:04:26
我想要的不仅仅是一组数据的主分组，还包括下一个分组级别。在我的例子中，汇总汇总了一个月(行)的值，但我也希望汇总销售代表(列)的值。
http://sqlfiddle.com/#!9/60608/1
我迄今尽了最大努力：
CREATE TABLE IF NOT EXISTS SALES (
Month INT(3),
Rep CHAR(60),
Val DECIMAL (20 , 6 )
);
INSERT INTO SALES (Month, Rep, Val) VALUES
('9','Rep1',10),
('9','Rep1',20),
('9','Rep2',20),
('9','Rep2',30),
('9','Rep2',40),
('10','Rep1',50),
('10','Rep1',60),
('10','Rep2',70),
('11','Rep2',80);
SELECT Month, IFNULL(Rep, 'Total'), SUM(Val) As TotalValue
FROM SALES
GROUP BY FIELD(Month,
'9',
'10',
'11') ASC, Rep WITH ROLLUP生成以下内容
9 Rep1 30
9 Rep2 90
9 Total 120
10 Rep1 110
10 Rep2 70
10 Total 180
11 Rep2 80
11 Total 80
11 Total 380理想情况下，我希望它能表现出来
9 Rep1 30
9 Rep2 90
9 Total 120
10 Rep1 110
10 Rep2 70
10 Total 180
11 Rep2 80
11 Total 80
11 Total 380
0 Rep1 140
0 Rep2 240回答 1
Stack Overflow用户
回答已采纳
发布于 2017-08-02 13:37:32
例如(虽然在实践中我可能会处理应用程序代码中的摘要数据)：
SELECT * FROM (SELECT month,rep,SUM(val) val FROM sales GROUP BY month,rep WITH ROLLUP) x
UNION
SELECT 0,rep, SUM(val) FROM sales GROUP BY rep;
+-------+------+------------+
| month | rep | val |
+-------+------+------------+
| 9 | Rep1 | 30.000000 |
| 9 | Rep2 | 90.000000 |
| 9 | NULL | 120.000000 |
| 10 | Rep1 | 110.000000 |
| 10 | Rep2 | 70.000000 |
| 10 | NULL | 180.000000 |
| 11 | Rep2 | 80.000000 |
| 11 | NULL | 80.000000 |
| NULL | NULL | 380.000000 |
| 0 | Rep1 | 140.000000 |
| 0 | Rep2 | 240.000000 |
+-------+------+------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45461689
复制相关文章
相似问题
腾讯云开发者
