本文发表在 rolia.net 枫下论坛SELECT @max = maxSequence from #seqMax
难度不应该是这样：
SELECT @max = MAX(maxSequence) from #seqMax
同样的思路用ORACLE实现：
CREATE TABLE SourceTable(Item VARCHAR2(10),Line NUMBER, seq NUMBER);
INSERT INTO SourceTable VALUES ('A',1,0);
INSERT INTO SourceTable VALUES ('A',1,1);
INSERT INTO SourceTable VALUES ('A',1,2);
INSERT INTO SourceTable VALUES ('A',1,3);
INSERT INTO SourceTable VALUES ('A',1,4);
INSERT INTO SourceTable VALUES ('A',1,5);
INSERT INTO SourceTable VALUES ('B',1,0);
INSERT INTO SourceTable VALUES ('B',1,1);
INSERT INTO SourceTable VALUES ('B',1,2);
INSERT INTO SourceTable VALUES ('B',1,3);
INSERT INTO SourceTable VALUES ('B',1,4);
INSERT INTO SourceTable VALUES ('B',1,5);
INSERT INTO SourceTable VALUES ('B',1,8);
INSERT INTO SourceTable VALUES ('B',1,9);
INSERT INTO SourceTable VALUES ('A',2,1);
INSERT INTO SourceTable VALUES ('A',2,2);
INSERT INTO SourceTable VALUES ('A',2,5);
INSERT INTO SourceTable VALUES ('A',2,7);
INSERT INTO SourceTable VALUES ('A',2,8);
SELECT b.item,b.line,a.n
FROM (SELECT LEVEL-1 n FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(seq)+1 FROM SourceTable)) a
LEFT JOIN (SELECT s.*,MAX(seq) OVER(PARTITION BY item,line) maxseq FROM SourceTable s) b
PARTITION BY (b.item,b.line,b.maxseq)
ON (A.n=b.seq)
WHERE b.seq IS NULL AND a.n<=b.maxseq;
ITEM LINE N
---------- ---------- ----------
A 2 0
A 2 3
A 2 4
A 2 6
B 1 6
B 1 7
6 rows selected.
ORACLE的PARTITION OUTER JOIN免除了中间一次笛卡尔积。更多精彩文章及讨论，请光临枫下论坛 rolia.net