sqlite - SQLite查询可将连续范围的数字分为不同的分组集

Question

我想在下面获取此表的岛屿：

Group    MemberNo
A           100
A           101
A           200
A           201
A           202
A           203
X           100
X           101
A           204
X           301
X           302
A           500
A           600

我想使用SQL（孤岛）获得此结果：

Group  FromMemberNo      ToMemberNo
A             100             101
A             200             204
X             100             101
X             301             302
A             500             500
A             600             600

我已经看到了很多代码/论坛，但是不能与SQLite一起使用，因为SQLite没有CTE。

100-101是连续的，因此它将被分组为一个。

有谁知道如何在SQLite中做到这一点？

Answer 1

最快的方法是循环查看此表的有序记录，然后手动收集岛。

在纯SQL（作为一种面向集合的语言）中，这并不是那么容易。

首先，我们找出哪个记录​​是一个岛上的第一个记录。第一个记录没有先前的记录，即具有相同组但MemberNo较小的记录：

SELECT "Group",
       MemberNo AS FromMemberNo
FROM ThisTable AS t1
WHERE NOT EXISTS (SELECT 1
                  FROM ThisTable AS t2
                  WHERE t2."Group" = t1."Group"
                    AND t2.MemberNo = t1.MemberNo - 1)

要查找某个岛的最后一条记录，我们必须找到具有最大 MemberNo且仍属于同一岛（即具有相同组）并且岛中所有 MemberNo都是连续的记录。
我们通过计算第一个记录和最后一个记录中它们之间的差值来检测连续的 MemberNo。
岛的最后一个 MemberNo带有组 G和第一个 MemberNo M可以这样计算：

SELECT MAX(MemberNo) AS LastMemberNo
FROM ThisTable AS t3
WHERE t3."Group" = G
  AND t3.MemberNo - M + 1 = (SELECT COUNT(*)
                             FROM ThisTable AS t4
                             WHERE t4."Group" = G
                               AND t4.MemberNo BETWEEN M AND t3.MemberNo)

最后，将其插入第一个查询：

SELECT "Group",
       MemberNo AS FromMemberNo,
       (SELECT MAX(MemberNo)
        FROM ThisTable AS t3
        WHERE t3."Group" = t1."Group"
          AND t3.MemberNo - t1.MemberNo + 1 = (SELECT COUNT(*)
                                               FROM ThisTable AS t4
                                               WHERE t4."Group" = t1."Group"
                                                 AND t4.MemberNo BETWEEN t1.MemberNo AND t3.MemberNo)
       ) AS LastMemberNo
FROM ThisTable AS t1
WHERE NOT EXISTS (SELECT 1
                  FROM ThisTable AS t2
                  WHERE t2."Group" = t1."Group"
                    AND t2.MemberNo = t1.MemberNo - 1)