如何在sql中获取序列号的范围? [英] how to obtain ranges of serial numbers in sql?

问题描述

我有一个表，其中有一个称为扇区的字段，每个扇区通常是1,2,3,4,5,6,7等.

I have a table with a field called sector, each sector is usually something like 1,2,3,4,5,6,7,etc.

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

I want to show available sectors in an application, I thought that showing all 1,2,3,4,5,6,7 is dumb so I should show "1 to 7" instead.

问题在于有时扇区会跳过一个数字，例如1,2,3,5,6,7. 所以我想显示1到3、5到7之类的东西.

The problem is that sometimes the sectors skip one number like this 1,2,3, 5,6,7. So I want to show something like 1 to 3, 5 to 7.

如何在sql中查询它以在我的应用中显示?

how could I query this in sql to show in my app?

推荐答案

某些DBMS可能具有一些OLAP功能，可以很容易地编写此类查询，但是IBM Informix Dynamic Server(IDS)尚不具有此类功能.

Some DBMS might have some OLAP functionality that makes it easy to write such queries, but IBM Informix Dynamic Server (IDS) does not yet have such functions.

为了具体起见，我们假设您的表被称为"ProductSectors"，其结构类似于:

Let's assume, for sake of concreteness, that your table is called 'ProductSectors' and has a structure like:

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的最小和最大连续值的列表.当不存在小于最小值的值且不存在大于最大值的值且该范围内没有间隙时，范围是连续的.这是一个复杂的查询:

What you are seeking within a particular ProductID is a list of the minimum and maximum contiguous values of Sector. A range is contiguous when there is no value one smaller than the minimum and no value one bigger than the maximum and there is no gap within the range. This is a complex query:

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;

这是使用示例数据进行整体查询的痕迹:

And here is a trace of the overall query working with sample data:

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

还有一些示例数据，但差距很大:

And some sample data, with various gaps:

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");

必需的输出-也是实际的输出:

Required output - also the actual output:

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上具有预期的结果， SQLCMD 86.04.

With the expected results on MacOS X 10.6.2, IDS 11.50.FC4W1, SQLCMD 86.04.

这篇关于如何在sql中获取序列号的范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！