在SQLite数据库中查找连续的id块

Question

我有一个包含数字列表的SQLite表。如下所示：

1
2
3
8
9
11
12
13
14
15
18
19

我需要将它们转换为具有连续ids的块。例如,

1-3 
8-9 
11-15 
18-19

这是否可以只使用SQL，或者我是否需要遍历并比较以前和当前...

Answer 1

我找到了一个使用临时表的解决方案。

首先，我们必须确定起点和终点：

http://sqlfiddle.com/#!5/fdc26/13

SELECT
  a.x AS ax,
  CASE WHEN p.x IS NULL THEN 1
       WHEN n.x IS NULL THEN 2
       ELSE                  0
  END AS begin_or_end
FROM       num AS a
LEFT  JOIN num AS n ON a.x + 1 = n.x /* n: next */
LEFT  JOIN num AS p ON a.x - 1 = p.x /* p: prev */
WHERE p.x IS NULL
   OR n.x IS NULL
ORDER BY a.x ASC;

或者，您可以将其拆分为两个查询：

SELECT a.x AS begin_point
FROM       num AS a
LEFT  JOIN num AS p ON a.x - 1 = p.x /* p: prev */
WHERE p.x IS NULL
ORDER BY a.x ASC;

SELECT a.x AS end_point
FROM       num AS a
LEFT  JOIN num AS n ON a.x + 1 = n.x /* n: next */
WHERE n.x IS NULL
ORDER BY a.x ASC;

在我的示例中，我选择了第一个选项来创建一个名为lohi的临时表。我利用了这样一个事实，即每个表都有一个自动主键(有关更多详细信息，请参阅http://www.sqlite.org/lang_createtable.html /ROWID和整数主键)

最后一个查询：

http://sqlfiddle.com/#!5/21770/1

SELECT lo.ax, hi.ax
FROM       lohi AS lo
INNER JOIN lohi AS hi ON lo.rowid+1 = hi.rowid
WHERE lo.rowid % 2;