|
|
Posted by avode on 10/16/06 09:01
I want to suggest the next 2 queries
SELECT MIN(seq) AS start, MAX(seq) + 1 AS stop
FROM(SELECT T1.seq, T1.seq - COUNT(*) AS g
FROM (SELECT DISTINCT S1.seq
FROM Sequence AS S1,
Intervals AS I1
WHERE I1.start <= S1.seq AND I1.stop >= S1.seq + 1) AS
T1,
(SELECT DISTINCT S2.seq
FROM Sequence AS S2,
Intervals AS I2
WHERE I2.start <= S2.seq AND I2.stop >= S2.seq + 1) AS
T2
WHERE T2.seq <= T1.seq
GROUP BY T1.seq) AS T3
GROUP BY g;
SELECT MIN(seq) AS start, MAX(seq) + 1 AS stop
FROM (SELECT DISTINCT S1.seq, S1.seq - COUNT(DISTINCT S2.seq) AS g
FROM Sequence AS S1
, Sequence AS S2
, Intervals AS I1
, Intervals AS I2
WHERE I1.start <= S1.seq AND I1.stop >= S1.seq + 1
AND I2.start <= S2.seq AND I2.stop >= S2.seq + 1
AND S2.seq <= S1.seq
GROUP BY S1.seq) AS R
GROUP BY g;
The first query is cheaper in terms of I/O (i hope :)
The latter query is more compact :)
Both use a Sequence table
CREATE TABLE Sequence(seq INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Sequence(seq)
SELECT (thousand * 1000 + hundred * 100 + ten * 10 + unit) AS seq
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Thousands(thousand)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Hundreds(hundred)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit)
UNION ALL
SELECT 10000;
---
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)
Navigation:
[Reply to this message]
|