SQL 中的连续序列

Question

有一个包含以下字段的表：

订单、组、序列，

要求给定组中的所有订单形成连续序列。 例如：1、2、3、4 或 4、5、6、7。 如何使用单个 SQL 查询检查哪些订单不符合此规则？ 谢谢。

Example data:

Order   Group   Sequence
1   1   3
2   1   4
3   1   5
4   1   6
5   2   3
6   2   4
7   2   6

Expected result:
Order
5
6
7

如果查询仅返回序列错误的组（示例数据为 2），也可接受。

Answer 1

假设序列已生成，因此不能重复：

SELECT group
 FROM theTable
 GROUP BY group
 HAVING MAX(Sequence) - MIN(Sequence) <> (COUNT(*) - 1);

Answer 2

这个怎么样？

从表中选择组
按组分组
具有 count(Sequence) <= max(Sequence)-min(Sequence)

[编辑] 这假设 Sequence 不允许在特定组内出现重复项。 使用以下方法可能会更好：
count != max - min + 1

[再次编辑] D'oh，仍然不完美。 不过，另一个清除重复项的查询可以解决这个问题。

[编辑最后一个] 原始查询在 sqlite 中运行良好，这是我可用于快速测试的。 它比 SQL Server 更宽容。 感谢贝尔的指点。

Answer 3

我个人认为我会考虑重新考虑这个要求。 关系数据库的本质是，由于记录回滚，很容易出现序列间隙。 例如，假设一个订单开始在其中创建四个项目，但其中一个因某些原因失败并被回滚。 如果您手动预先计算序列，则会出现间隙，因为回滚的不是最后一个。 在其他情况下，由于多个用户几乎同时查找序列值，或者客户在最后一刻从订单中删除了一条记录，您可能会遇到间隙。 老实说，您希望从父子关系中无法获得的连续序列中获得什么？

Answer 4

此 SQL 选择没有连续序列的订单 3 和 4。

DECLARE @Orders TABLE ([Order] INTEGER, [Group] INTEGER, Sequence INTEGER)

INSERT INTO @Orders VALUES (1, 1, 0)
INSERT INTO @Orders VALUES (1, 2, 0)
INSERT INTO @Orders VALUES (1, 3, 0)

INSERT INTO @Orders VALUES (2, 4, 0)
INSERT INTO @Orders VALUES (2, 5, 0)
INSERT INTO @Orders VALUES (2, 6, 0)

INSERT INTO @Orders VALUES (3, 4, 0)
INSERT INTO @Orders VALUES (3, 6, 0)

INSERT INTO @Orders VALUES (4, 1, 0)
INSERT INTO @Orders VALUES (4, 2, 0)
INSERT INTO @Orders VALUES (4, 8, 0)

SELECT o1.[Order]
FROM @Orders o1
     LEFT OUTER JOIN @Orders o2 ON o2.[Order] = o1.[Order] AND o2.[Group] = o1.[Group] + 1
WHERE o2.[Order] IS NULL
GROUP BY o1.[Order]
HAVING COUNT(*) > 1

Answer 5

所以你的表的形式是

Order Group Sequence
1     1     4
1     1     5
1     1     7

..并且你想找出 1,1,6 丢失了吗？

您

select
  min(Sequence) MinSequence, 
  max(Seqence) MaxSequence 
from 
  Orders 
group by 
  [Order], 
  [Group]

可以使用它找出给定订单和组的范围。

现在，您可以使用特殊的数字表来模拟正确的数据，该表仅包含您可以用于序列的每个数字。 这里是此类数字表的一个很好的示例。 如何创建它并不重要，您还可以创建一个包含从 x 到 y 的所有数字的 Excel 文件，然后导入该 Excel 工作表。

在我的示例中，我假设有一个名为“Numbers”的数字表，只有一列“n”：

select 
  [Order], 
  [Group], 
  n Sequence
from
  (select min(Sequence) MinSequence, max(Seqence) MaxSequence from [Table] group by [Order], [Group]) MinMaxSequence
  left join Numbers on n >= MinSequence and n <= MaxSequence

将该 SQL 放入一个新视图中。 在我的示例中，我将调用该视图“vwCorrectOrders”。

这将为您提供序列连续的数据。 现在您可以将该数据与原始数据连接起来，以找出缺少哪些序列：

select 
  correctOrders.*
from
  vwCorrectOrders co 
  left join Orders o on 
      co.[Order] = o.[Order] 
  and co.[Group] = o.[Group]
  and co.Sequence = o.Sequence
where
  o.Sequence is null

应该给您

Order Group Sequence
1     1     6

Answer 6

一段时间后，我想出了以下解决方案。 这似乎有效，但效率极低。 请添加任何改进建议。

SELECT OrdMain.Order
  FROM ((Orders AS OrdMain
  LEFT OUTER JOIN Orders AS OrdPrev ON (OrdPrev.Group = OrdMain.Group) AND (OrdPrev.Sequence = OrdMain.Sequence - 1))
  LEFT OUTER JOIN Orders AS OrdNext ON (OrdNext.Group = OrdMain.Group) AND (OrdNext.Sequence = OrdMain.Sequence + 1))
WHERE ((OrdMain.Sequence < (SELECT MAX(Sequence) FROM Orders OrdMax WHERE (OrdMax.Group = OrdMain.Group))) AND (OrdNext.Order IS NULL)) OR
      ((OrdMain.Sequence > (SELECT MIN(Sequence) FROM Orders OrdMin WHERE (OrdMin.Group = OrdMain.Group))) AND (OrdPrev.Order IS NULL))