how to obtain ranges of serial numbers in sql?
I have a table with a field called sector, each sector is usually something like 1,2,3,4,5,6,7,etc.
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.
The problem is that sometim开发者_运维技巧es 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.
how could I query this in sql to show in my app?
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.
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)
);
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
With the expected results on MacOS X 10.6.2, IDS 11.50.FC4W1, SQLCMD 86.04.
This is called "Gaps" in sql. Here is a detailed article "Article"
ok, I have been looking deeper and found this
It works :), hope it helps someone as it helped me.
精彩评论