sql中如何获取序列号的范围？

Question

我有一个表，其中有一个名为扇区的字段，每个扇区通常类似于 1、2、3、4、5、6、7 等。

我想显示应用程序中的可用扇区，我认为显示所有 1,2,3,4,5,6,7 很愚蠢，所以我应该显示“1 到 7”。

问题是有时扇区会跳过一个数字，例如 1、2、3、5、6、7。 所以我想显示类似 1 到 3、5 到 7 的内容。

我如何在 sql 中查询它以在我的应用程序中显示？

Answer 1

某些 DBMS 可能具有一些 OLAP 功能，可以轻松编写此类查询，但 IBM Informix Dynamic Server (IDS) 尚不具备此类功能。

为了具体起见，我们假设您的表名为“ProductSectors”，其结构如下：

CREATE TABLE ProductSectors
(
    ProductID    INTEGER NOT NULL,
    Sector       INTEGER NOT NULL CHECK (Sector > 0),
    Name         VARCHAR(20) NOT NULL,
    PRIMARY KEY (ProductID, Sector)
);

您在特定 ProductID 中查找的是 Sector 的最小和最大连续值的列表。当不存在比最小值小一的值和不比最大值大一的值且范围内不存在间隙时，范围是连续的。这是一个复杂的查询：

SELECT P1.ProductID, P1.Sector AS Min_Sector, P2.Sector AS Max_Sector
  FROM ProductSectors P1 JOIN ProductSectors P2
    ON P1.ProductID = P2.ProductID
   AND P1.Sector   <= P2.Sector
 WHERE NOT EXISTS (SELECT *     -- no entry one smaller
                     FROM ProductSectors  P6
                    WHERE P1.ProductID  = P6.ProductID
                      AND P1.Sector - 1 = P6.Sector
                  )
   AND NOT EXISTS (SELECT *     -- no entry one larger
                     FROM ProductSectors  P5
                    WHERE P2.ProductID  = P5.ProductID
                      AND P2.Sector + 1 = P5.Sector
                  )
   AND NOT EXISTS (SELECT *     -- no gaps between P1.Sector and P2.Sector
                     FROM ProductSectors P3
                    WHERE P1.ProductID = P3.ProductID
                      AND P1.Sector   <= P3.Sector
                      AND P2.Sector   >  P3.Sector
                      AND NOT EXISTS (SELECT *
                                        FROM ProductSectors P4
                                       WHERE P4.ProductID = P3.ProductID
                                         AND P4.Sector    = P3.Sector + 1
                                     )
                  )
 ORDER BY P1.ProductID, Min_Sector;

这里是使用示例数据的整个查询的跟踪：

CREATE TEMP TABLE productsectors
(
    ProductID   INTEGER NOT NULL,
    Sector      INTEGER NOT NULL CHECK(Sector > 0),
    Name        VARCHAR(20),
    PRIMARY KEY (ProductID, Sector)
);

以及一些具有各种间隙的示例数据：

INSERT INTO ProductSectors VALUES(101, 1, "101:1");
INSERT INTO ProductSectors VALUES(101, 2, "101:2");
INSERT INTO ProductSectors VALUES(101, 3, "101:3");
INSERT INTO ProductSectors VALUES(101, 4, "101:4");
INSERT INTO ProductSectors VALUES(101, 5, "101:5");
INSERT INTO ProductSectors VALUES(101, 6, "101:6");
INSERT INTO ProductSectors VALUES(101, 7, "101:7");
INSERT INTO ProductSectors VALUES(102, 1, "102:1");
INSERT INTO ProductSectors VALUES(102, 2, "102:2");
INSERT INTO ProductSectors VALUES(102, 4, "102:4");
INSERT INTO ProductSectors VALUES(102, 5, "102:5");
INSERT INTO ProductSectors VALUES(102, 6, "102:6");
INSERT INTO ProductSectors VALUES(102, 7, "102:7");
INSERT INTO ProductSectors VALUES(103, 1, "103:1");
INSERT INTO ProductSectors VALUES(103, 2, "103:2");
INSERT INTO ProductSectors VALUES(103, 4, "103:4");
INSERT INTO ProductSectors VALUES(103, 6, "103:6");
INSERT INTO ProductSectors VALUES(103, 7, "103:7");
INSERT INTO ProductSectors VALUES(104, 1, "104:1");
INSERT INTO ProductSectors VALUES(104, 2, "104:2");
INSERT INTO ProductSectors VALUES(104, 3, "104:3");
INSERT INTO ProductSectors VALUES(104, 6, "104:6");
INSERT INTO ProductSectors VALUES(104, 7, "104:7");
INSERT INTO ProductSectors VALUES(105, 1, "105:1");
INSERT INTO ProductSectors VALUES(105, 4, "105:4");
INSERT INTO ProductSectors VALUES(105, 5, "105:5");
INSERT INTO ProductSectors VALUES(105, 7, "105:7");
INSERT INTO ProductSectors VALUES(106, 1, "106:1");
INSERT INTO ProductSectors VALUES(106, 2, "106:1");
INSERT INTO ProductSectors VALUES(106, 3, "106:1");
INSERT INTO ProductSectors VALUES(106, 7, "106:7");
INSERT INTO ProductSectors VALUES(107, 7, "107:7");
INSERT INTO ProductSectors VALUES(108, 8, "108:8");
INSERT INTO ProductSectors VALUES(108, 9, "108:9");

所需的输出 - 也是实际的输出：

101|1|7 
102|1|2 
102|4|7 
103|1|2 
103|4|4 
103|6|7 
104|1|3 
104|6|7 
105|1|1 
105|4|5 
105|7|7 
106|1|3 
106|7|7 
107|7|7 
108|8|9 

MacOS X 10.6.2，IDS 11.50.FC4W1，SQLCMD 86.04。

Answer 2

这在sql中称为“间隙”。这是一篇详细的文章“文章”

Answer 3

好的，我一直在深入研究并发现 这

它有效:)，希望它能帮助别人，就像它帮助我一样。