1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
| CREATE TABLE T_LIGNES (COL1 INT, COL2 INT, COL3 INT)
INSERT INTO T_LIGNES VALUES (1, 1, 1)
INSERT INTO T_LIGNES VALUES (1, 2, 1)
INSERT INTO T_LIGNES VALUES (1, 1, 2)
INSERT INTO T_LIGNES VALUES (1, 3, 1)
INSERT INTO T_LIGNES VALUES (1, 1, 3)
INSERT INTO T_LIGNES VALUES (2, 1, 1)
INSERT INTO T_LIGNES VALUES (2, 2, 1)
INSERT INTO T_LIGNES VALUES (2, 1, 2)
INSERT INTO T_LIGNES VALUES (2, 3, 1)
INSERT INTO T_LIGNES VALUES (2, 1, 3)
INSERT INTO T_LIGNES VALUES (3, 1, 1)
INSERT INTO T_LIGNES VALUES (3, 2, 1)
INSERT INTO T_LIGNES VALUES (3, 1, 2)
INSERT INTO T_LIGNES VALUES (3, 3, 1)
INSERT INTO T_LIGNES VALUES (3, 1, 3)
INSERT INTO T_LIGNES VALUES (4, 1, 1)
INSERT INTO T_LIGNES VALUES (4, 2, 1)
INSERT INTO T_LIGNES VALUES (4, 1, 2)
INSERT INTO T_LIGNES VALUES (4, 3, 1)
INSERT INTO T_LIGNES VALUES (4, 1, 3)
INSERT INTO T_LIGNES VALUES (5, 1, 1)
INSERT INTO T_LIGNES VALUES (5, 2, 1)
INSERT INTO T_LIGNES VALUES (1, 1, 2)
INSERT INTO T_LIGNES VALUES (5, 3, 1)
INSERT INTO T_LIGNES VALUES (5, 1, 3)
-- selection de 10 lignes en order by col1, col2 dont 3 lignes avec col3 = 1
-- 3 lignes avec col3 = 1
SELECT COL1, COL2, COL3
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY col1, col2) AS N
FROM T_LIGNES
WHERE COL3 = 1) AS T
WHERE N <= 3
UNION
-- 7 lignes avec d'autres valeurs en ordre pour col1, col2 mais pas COL3 = 1
SELECT COL1, COL2, COL3
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY col1, col2) AS N
FROM (SELECT *
FROM T_LIGNES
EXCEPT
SELECT COL1, COL2, COL3
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY col1, col2) AS N
FROM T_LIGNES
WHERE COL3 = 1) AS T
WHERE N <= 3) AS T
WHERE COL3 <> 1 ) AS TT
WHERE N <= 7
COL1 COL2 COL3
----------- ----------- -----------
1 1 1
1 2 1
1 3 1
1 1 2
1 1 3
2 1 2
2 1 3
3 1 2
3 1 3
4 1 2 |
Partager