SQL GROUP BY:间隔是否连续?

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

这个想法是说你有下表.

The idea is that say you have the following table.

------------- | oID | Area| ------------- | 1 | 5 | | 2 | 2 | | 3 | 3 | | 5 | 3 | | 6 | 4 | | 7 | 5 | -------------

如果可以通过连续性进行分组，则此伪查询

If grouping by continuity is possible this pseudo query

SELECT SUM(Area) FROM sample_table GROUP BY CONTINUITY(oID)

会返回

------------- | SUM(Area) | ------------- | 10 | | 12 | -------------

如果在oID处出现连续性中断，或者缺少oID时，代表oID 4的条目.

Where the continuity break arises at oID or rather the lack thereof an entry representing oID 4.

这种功能是否存在于Sql的标准功能之内?

Does such functionality exist within the standard functions of Sql?

推荐答案

"SQL的标准函数"中没有此类功能，但是可以通过一些技巧来获得所需的结果集.

There is no such functionality in "standard functions of SQL", but it is possible to get the desired result set by using some tricks.

使用下面说明的子查询，我们创建一个虚拟字段，您可以在外部查询中使用该字段GROUP BY.每次oID序列中存在间隙时，此虚拟字段的值都会增加.这样，我们为每个数据岛"创建一个标识符:

With the subquery illustrated below we create a virtual field which you can use to GROUP BY in the outer query. The value of this virtual field is incremented each time when there is a gap in the sequence of oID. This way we create an identifier for each of those "data islands":

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);

我要感谢Quassnoi指出此相关问题中的技巧;-)

I need to thank Quassnoi for pointing out this trick in my related question ;-)

更新:在示例查询中添加了测试表和数据，并修复了重复的列名.

UPDATE: added test table and data and fixed duplicate column name in example query.