我有一个包含数字列表的SQLite表。如下所示:
1
2
3
8
9
11
12
13
14
15
18
19
我需要将它们转换为具有顺序 ID 的块。例如
1-3
8-9
11-15
18-19
这是否可以仅使用 SQL,或者我是否需要循环并将以前与当前进行比较......
我找到了使用临时表的解决方案。
首先,我们必须确定起点和终点:
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
的临时表。我使用每个表都有一个自动rowid
的事实(有关更多详细信息,请参阅 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;