我的sql查询是
SELECT COUNT(*) as count FROM <DB> GROUP BY InputTime ORDER BY InputTime) A GROUP BY HOUR(InputTime) WITH ROLLUP;
结果是
+-------+
| count |
+-------+
| 62 |
| 200 |
| 10 |
| 272 |
+-------+
最后一行是以前值的总和。如何使最后一排到第一排像这样?
+-------+
| count |
+-------+
| 272 |
| 62 |
| 200 |
| 10 |
+-------+
我也会选择小时(inputtime(,并按它排序。汇总行将有一个null,并首先进行nulls排序。
也许是这样的:
SELECT IFNULL(hours,'Total'), count
FROM
(SELECT HOUR(InputTime) hours, COUNT(*) AS count
FROM DBname where InputTime>= '2021-07-07' AND InputTime <='2021-07-07 23:56:59' GROUP BY HOUR(InputTime) WITH ROLLUP) V
ORDER BY hours ASC;
由于ROLLUP
为子查询中的hours
列返回了NULL
,因此外部查询中的ORDER BY hours
将把它放在首位";。。在MySQL中,NULL值被认为低于任何非NULL值,因此,当顺序为ASC(升序(时,NULL值首先出现,而当顺序为DESC(降序(时,则NULL值最后出现">
演示小提琴