计算SQL中的连续数值

本文介绍了计算SQL中的连续数值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有关此方案的问题已经很多，但是我无法在方案中复制答案.

There are already many existing questions about this scenario, however I am unable to replicate the answers in my scenario.

我有以下示例数据集:

ID Number | Values 754321 0 754321 0 754321 0 754321 0 754321 1 754321 0 754321 1 754321 0 754321 2 754321 0 754329 3 754329 4 754329 5 754329 6 754329 7 754329 8 754329 9

我希望输出ID Number的SQL查询的值连续显示"0"的次数.因此，对于上表，我希望获得如下输出:

I want the SQL query that outputs the ID Number with the number of times the value of "0" appears consecutively. So, for the above table I would like to get the output as follows:

ID Number Count of Consecutive 0 Values 754321 4

推荐答案

如果运行的MySQL版本不支持窗口函数，则可以使用变量来实现此功能:

If you're running a version of MySQL that doesn't support window functions, you can implement this functionality using variables:

SELECT `ID Number`, MAX(cnt) AS `Max Consecutive 0 Values` FROM (SELECT `ID Number`, SUM(`Values` = 0) AS cnt FROM (SELECT `ID Number`, `Values`, @cnz:= CASE WHEN `Values` != 0 THEN @cnz + 1 ELSE @cnz END AS cnz FROM data CROSS JOIN (SELECT @cnz := 0) init ORDER BY date ) c GROUP BY `ID Number`, cnz) s GROUP BY `ID Number`

输出

ID Number Max Consecutive 0 Values 754321 4 754329 0

SQLFiddle上的演示