“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);
我要感谢夸斯诺伊指出我的相关问题中的这个技巧 https://stackoverflow.com/questions/1136597/group-by-for-continuous-rows-in-sql ;-)
更新:添加了测试表和数据,并修复了示例查询中的重复列名。