С проблемой СВЕРТКИ в порядке диапазона

#mysql

Вопрос:

После добавления свертки WITH в оператор GROUP BY диапазоны были переупорядочены. Как это можно исправить?

Вот код

                  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.

1. Почему тег mysqli? Здесь нет кода приложения.

2. @Strawberry -извините за мою ошибку.

3. Если вы добавите ‘ 0’ к ORDER BY, я подозреваю, что это исправит ситуацию, но у меня возникнет соблазн сделать что-то подобное в коде приложения.

4. @Strawberry — это сработало. Однако он скопировал последний диапазон 2001-2500, когда он должен быть нулевым. Есть ли способ удалить дублированный диапазон?

5. @toff какая у вас версия MySQL?

Ответ №1:

Столбец 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