mysql 日期连续性_mysql – SQL GROUP BY：连续性的间隔？

“SQL的标准函数”中没有这样的功能,但可以通过使用一些技巧来获得所需的结果集.

通过下面说明的子查询,我们创建了一个虚拟字段,您可以在外部查询中使用GROUP BY.每当oID序列中存在间隙时,该虚拟字段的值就会递增.这样我们就为每个“数据岛”创建了一个标识符：

SELECT SUM(Area), COUNT(*) AS Count_Rows

FROM (

/* @group_enumerator is incremented each time there is a gap in oIDs continuity */

SELECT @group_enumerator := @group_enumerator + (@prev_oID != oID - 1) AS group_enumerator,

@prev_oID := oID AS prev_oID,

sample_table.*

FROM (

SELECT @group_enumerator := 0,

@prev_oID := -1

) vars,

sample_table

/* correct order is very important */

ORDER BY

oID

) q

GROUP BY

group_enumerator

测试表和数据生成：

CREATE TABLE sample_table (oID INT auto_increment, Area INT, PRIMARY KEY(oID));

INSERT INTO sample_table (oID, Area) VALUES (1,5), (2,2), (3,3), (5,3), (6,4), (7,5);

更新：在示例查询中添加了测试表和数据以及修复的重复列名称.