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

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等。

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,4,5,6,7是愚蠢的，所以我应该显示“1到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.

问题是，有时扇区跳过一个数字，如1,2,3,5,6,7。所以我想展示1到3,5到7之类的东西。

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

我怎么能在sql中查询这个在我的应用程序中显示？

3 个解决方案

#1

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.

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

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

为了具体起见，我们假设您的表被称为“ProductSectors”并且具有如下结构：

CREATE TABLE ProductSectors
(
    ProductID    INTEGER NOT NULL,
    Sector       INTEGER NOT NULL CHECK (Sector > 0),
    Name         VARCHAR(20) NOT NULL,
    PRIMARY KEY (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:

您在特定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;

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

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

预期结果在MacOS X 10.6.2，IDS 11.50.FC4W1，SQLCMD 86.04上。

#2

This is called "Gaps" in sql. Here is a detailed article "Article"

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

#3

ok, I have been looking deeper and found this

好吧，我一直在寻找更深层次，并找到了这个

It works :), hope it helps someone as it helped me.

它工作:)，希望它帮助了一些人，因为它帮助了我。

#1