mysql - SQL 语句对相应的条目进行计数，同时具有严格的顺序(逻辑)

Question

我想在保留顺序的同时汇总生产计划中(相同)零件的数量。

我的表格是这样的:

-------------------------------------------------
|Part Number | Production Sequence Number | .... |
-------------------------------------------------
|           1|                           1| .... |
--------------------------------------------------
|           1|                           2| .... |
--------------------------------------------------
|           2|                           3| .... |
--------------------------------------------------
|           2|                           4| .... |
--------------------------------------------------
|           1|                           5| .... |
--------------------------------------------------

我需要计算连续相同部分的数量:

预期结果:

-------------------------------------------------
|Part Number | Nr of pieces in a row      | .... |
-------------------------------------------------
|           1|                           2| .... |
-------------------------------------------------
|           2|                           2| .... |
-------------------------------------------------
|           1|                           1| .... |
-------------------------------------------------

这可以只用 unsing SQL (MySQL) 来完成吗？

Answer 1

您可以使用变量来模拟数字，然后执行GROUP BY

SELECT id, COUNT(*)
FROM
(
    SELECT 
        @row_number:=CASE
            WHEN @seq_num = id THEN @row_number
            ELSE @row_number + 1
        END AS num,
        @seq_num:=id as id,
        seq_num
    FROM tab, (SELECT @row_number:=0, @seq_num := 1) t
    ORDER BY seq_num
) t
GROUP BY num, id

demo