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
| CREATE TABLE T_STOCK_STK
(STK_ID INT NOT NULL PRIMARY KEY,
STK_TYPE VARCHAR(16) NOT NULL)
INSERT INTO T_STOCK_STK VALUES (1, 'Souris')
INSERT INTO T_STOCK_STK VALUES (2, 'Souris')
INSERT INTO T_STOCK_STK VALUES (3, 'Souris')
INSERT INTO T_STOCK_STK VALUES (4, 'Souris')
INSERT INTO T_STOCK_STK VALUES (5, 'Souris')
INSERT INTO T_STOCK_STK VALUES (6, 'Souris')
INSERT INTO T_STOCK_STK VALUES (7, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (8, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (9, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (10, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (11, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (12, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (13, 'Ecran')
INSERT INTO T_STOCK_STK VALUES (14, 'Ecran')
SELECT ST1.STK_ID, ST1.STK_TYPE
FROM T_STOCK_STK ST1
LEFT OUTER JOIN T_STOCK_STK ST2
ON ST1.STK_ID > ST2.STK_ID
AND ST1.STK_TYPE = ST2.STK_TYPE
GROUP BY ST1.STK_ID, ST1.STK_TYPE
HAVING COUNT(ST2.STK_ID) < 3
STK_ID STK_TYPE
----------- ----------------
1 Souris
2 Souris
3 Souris
7 Ecran
8 Ecran
9 Ecran |
Partager