SQL 中的连续序列 [英] Continuous sequences in SQL

问题描述

有一个包含以下字段的表格:

顺序、组、序列

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

示例数据:订单组序列1 1 32 1 43 1 54 1 65 2 36 2 47 2 6预期结果:命令567

如果查询仅返回序列错误的组，则也接受，示例数据为 2.

解决方案

假设序列是生成的，因此不能被复制:

<前><代码>选择组从表中按组分组有 MAX(Sequence) - MIN(Sequence) <> (COUNT(*) - 1);

Having a table with the following fields:

Order,Group,Sequence

it is required that all orders in a given group form a continuous sequence. For example: 1,2,3,4 or 4,5,6,7. How can I check using a single SQL query what orders do not comply with this rule? Thank you.

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

Also accepted if the query returns only the group which has the wrong sequence, 2 for the example data.

解决方案

Assuming that the sequences are generated and therefore cannot be duplicated:

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

这篇关于SQL 中的连续序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！