1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| CREATE TABLE TXY (COL1 CHAR(1), COL2 INT)
INSERT INTO TXY VALUES ('A', NULL)
INSERT INTO TXY VALUES ('A', NULL)
INSERT INTO TXY VALUES ('A', NULL)
INSERT INTO TXY VALUES ('B', NULL)
INSERT INTO TXY VALUES ('B', NULL)
INSERT INTO TXY VALUES ('C', NULL)
CREATE TABLE T_NUM (N INT)
INSERT INTO T_NUM VALUES (1)
INSERT INTO T_NUM VALUES (2)
INSERT INTO T_NUM VALUES (3)
...
INSERT INTO T_NUM VALUES (99999)
SELECT COL1, N
FROM (SELECT COL1, COUNT(*) AS C
FROM TXY
GROUP BY COL1) AS T
INNER JOIN T_NUM
ON N BETWEEN 1 AND C
ORDER BY COL1, N |
Partager