在GROUP BY语句中添加WITH ROLLUP后，对范围进行了重新排序。如何解决这个问题？
这是代码
SUM(product.product_id = 1) AS Soda,
SUM(product.product_id = 2) AS Liquor,
SUM(product.product_id = 3) AS Lemon,
SUM(product.product_id = 4) AS Mango,
SUM(product.product_id = 5) AS Inhaler,
SUM(1) AS Count
FROM line_item
JOIN product USING (product_id)
JOIN ( SELECT 0 lowest, 500 highest UNION
SELECT 501 , 1000 UNION
SELECT 1001 , 1500 UNION
SELECT 1501 , 2000 UNION
SELECT 2001 , 2500 ) ranges ON product.price * line_item.quantity BETWEEN ranges.lowest AND ranges.highest
GROUP BY Revenue WITH ROLLUP;
Result:
+-------------+------+--------+-------+-------+---------+-------+
| Revenue | Soda | Liquor | Lemon | Mango | Inhaler | Count |
+-------------+------+--------+-------+-------+---------+-------+
| 0 - 500 | 4 | 0 | 4 | 0 | 1 | 9 |
| 1001 - 1500 | 0 | 1 | 0 | 2 | 2 | 5 |
| 1501 - 2000 | 0 | 2 | 0 | 0 | 1 | 3 |
| 2001 - 2500 | 0 | 1 | 0 | 0 | 0 | 1 |
| 501 - 1000 | 0 | 0 | 0 | 2 | 0 | 2 |
| NULL | 4 | 4 | 4 | 4 | 4 | 20 |
+-------------+------+--------+-------+-------+---------+-------+
The range 501 - 1000 moved to the bottom, it should be next to the 0-500 range.
列Revenue是一个字符串，因此结果按字母顺序排序
为了将列排序为数字，解决方案是将Revenue强制转换为数字，如：
ORDER BY Revenue IS NULL, Revenue + 0
但正如我在MySql 8.0.22中测试的那样(使用之前的数据(，由于某种原因，它不起作用(可能是一个错误？(
不管怎样，你也应该试试
有效的代码是：
GROUP BY ranges.lowest, ranges.highest WITH ROLLUP
HAVING GROUPING(ranges.lowest) = 1 OR GROUPING(ranges.highest) = 0
ORDER BY GROUPING(ranges.lowest), ranges.lowest
请参阅演示
结果：
> Revenue | Soda | Liquor | Lemon | Mango | Inhaler | Count
> :-------- | ---: | -----: | ----: | ----: | ------: | ----:
> 0-500 | 4 | 0 | 4 | 0 | 1 | 9
> 501-1000 | 0 | 0 | 0 | 2 | 0 | 2
> 1001-1500 | 0 | 1 | 0 | 2 | 2 | 5
> 1501-2000 | 0 | 2 | 0 | 0 | 1 | 3
> 2001-2500 | 0 | 1 | 0 | 0 | 0 | 1
> null | 4 | 4 | 4 | 4 | 4 | 20