在MySQL中创建交叉表查询
问题描述:
我有一个MySQL表,可以在一天中的不同时间点和不同位置跟踪某些总计。我试图创建一个查询,不仅将每个列,而且每个行。我到目前为止的查询总计每列,但我无法弄清楚如何获得每行的总数。在MySQL中创建交叉表查询
这是我的查询:
SELECT * FROM
(SELECT IFNULL(hour,"Total") as hour, SUM(location1), SUM(location2), SUM(location3), SUM(location4), SUM(location), FROM counts WHERE ay = 'AY1617' GROUP BY hour WITH ROLLUP) as crossdata
ORDER BY FIELD (hour,'8:00am','9:00am','10:00am','11:00am','12:00pm','1:00pm','2:00pm','3:00pm','4:00pm','5:00pm','6:00pm','7:00pm','8:00pm','9:00pm','10:00pm','11:00pm')
这是最终我想输出的样子:
hour location1 location2 location3 location4 totals
8am 4 3 2 1 10
9am 1 2 2 1 6
10am 2 3 2 3 10
totals 7 8 6 5 26
我怎样才能做到这一点?
答
值得一提的是,这不是交叉表查询。你没有将行转换为列。
我尝试此查询并得到结果,你想:
SELECT IFNULL(hour, 'Total') AS hour,
SUM(location1) AS location1,
SUM(location2) AS location2,
SUM(location3) AS location3,
SUM(location4) AS location4,
SUM(location1)+SUM(location2)+SUM(location3)+SUM(location4) AS totals
FROM counts
WHERE ay = 'AY1617'
GROUP BY hour WITH ROLLUP;
你真的应该使用TIME
数据类型,而不是为小时字符串。然后它只是正确排序。
+----------+-----------+-----------+-----------+-----------+--------+
| hourt | location1 | location2 | location3 | location4 | totals |
+----------+-----------+-----------+-----------+-----------+--------+
| 08:00:00 | 4 | 3 | 2 | 1 | 10 |
| 09:00:00 | 1 | 2 | 2 | 1 | 6 |
| 10:00:00 | 2 | 3 | 2 | 3 | 10 |
| Total | 7 | 8 | 6 | 5 | 26 |
+----------+-----------+-----------+-----------+-----------+--------+