Mysql查询对行进行编号分组

Question

我花了好几天的时间寻找一种显示组行紧凑连续编号的方法。产品可以是纸箱中的单一类型，也可以混合在一起。有些纸箱标记已经印好了，所以我不能重新排列纸箱标记。我有这张桌子：

+-----+------------+--------+-----------+
| qty | product_id |  Type  | carton_no |
+-----+------------+--------+-----------+
|  18 |        111 | single |         1 |
|  18 |        111 | single |         2 |
|  18 |        111 | single |         3 |
|  48 |        115 | single |         4 |
|  48 |        115 | single |         5 |
|  48 |        115 | single |         6 |
|  36 |        119 | single |         7 |
|  36 |        119 | single |         8 |
|  18 |        111 | single |         9 |
|  36 |        119 | single |        10 |
|  16 |        199 | single |        11 |
|  16 |        199 | single |        12 |
|   4 |        111 | mix    |        13 |
|   4 |        115 | mix    |        13 |
|   4 |        119 | mix    |        13 |
|   4 |        199 | mix    |        13 |
+-----+------------+--------+-----------+

文档处理器需要这样的视图：

+-----------+-----+------------+--------+
| Numbering | QTY | product_id |  Type  |
+-----------+-----+------------+--------+
| 1-4       |  72 |        111 | single |
| 5-7       | 144 |        115 | single |
| 8-10      | 108 |        119 | single |
| 11-12     |  32 |        199 | single |
| 13        |   4 |        111 | mix    |
| 13        |   4 |        115 | mix    |
| 13        |   4 |        119 | mix    |
| 13        |   4 |        199 | mix    |
+-----------+-----+------------+--------+

编号实际上是按类型product_id ASC计算每个product_id订单的纸箱总数。有什么想法吗？

Answer 1

WITH 
cte1 AS ( 
SELECT qty, 
product_id, 
Type, 
carton_no,
CASE WHEN product_id = LAG(product_id) OVER (ORDER BY carton_no)
THEN 0
ELSE 1
END new_group
FROM src ),
cte2 AS (
SELECT qty, 
product_id, 
Type, 
carton_no,
SUM(new_group) OVER (ORDER BY carton_no) group_num
FROM cte1
)
SELECT CASE WHEN MAX(carton_no) > MIN(carton_no)
THEN CONCAT(MIN(carton_no), '-', MAX(carton_no))
ELSE MIN(carton_no)
END Numbering ,
SUM(qty) QTY,
product_id,
ANY_VALUE(Type) Type
FROM cte2
GROUP BY group_num, product_id;

小提琴

Answer 2

WITH 
cte1 AS ( 
SELECT qty, 
product_id, 
Type, 
carton_no,
CASE WHEN product_id = LAG(product_id) OVER (ORDER BY type desc, product_id)
THEN 0
ELSE 1
END new_group
FROM src order by type desc, product_id ),
cte2 AS (
SELECT qty, 
product_id, 
Type, 
carton_no,
SUM(new_group) OVER (ORDER BY type desc, product_id) group_num
FROM cte1 ),
cte3 AS (
SELECT SUM(qty) QTY, 
product_id, 
Type, 
group_num,
carton_no,
count(group_num) sum,
LAG(count(group_num))  OVER () prevsum
FROM cte2 group by group_num order by type desc, carton_no
)
SELECT CASE WHEN group_num = 1 THEN CONCAT(group_num,'-', sum)
WHEN group_num <> 1 and Type = "mix"  and LAG(carton_no) OVER (ORDER BY carton_no) <> carton_no THEN CONCAT(SUM(prevsum) OVER (ORDER BY type desc, product_id) + 1)
WHEN group_num <> 1 and Type = "mix" and LAG(carton_no) OVER (ORDER BY carton_no) = carton_no THEN CONCAT(LAG(carton_no) OVER (ORDER BY carton_no))
WHEN group_num <> 1 and Type = "single" THEN CONCAT(SUM(prevsum) OVER (ORDER BY type desc, product_id) + 1,'-', SUM(prevsum) OVER (ORDER BY type desc, product_id) + sum)
END numbering,
qty,
product_id,
type
FROM cte3

我想我解决了这个问题，但代码在Workbench中工作，但不是在小提琴中。知道如何压缩它而不在小提琴中工作吗？