如何获取SQL中序列号的范围?

有些DBMS可能具有一些OLAP功能,使编写此类查询变得容易,但IBMInformixDynamicServer(IDS)还没有此类功能。

为了具体起见,假设您的表被称为“ProductSector”,其结构如下:

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

在特定的productID中,您要查找的是扇区的最小值和最大值的列表。当没有小于最小值的值,也没有大于最大值的值,并且范围内没有间隙时,范围是连续的。这是一个复杂的查询:

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. IDS 1150.FC4W1, SQLCMD 86.04。

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

好吧,我一直在深入研究,发现 this

它起作用了:),希望它能像帮助我一样帮助别人。