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
|
WITH matable AS
(SELECT 'Alpha' nom, 'AA' note, 2006 dt, 3 rang
FROM DUAL
UNION ALL
SELECT 'Alpha', 'NR', 1998, 27
FROM DUAL
UNION ALL
SELECT 'Alpha', 'A+', 2004, 5
FROM DUAL
UNION ALL
SELECT 'Beta', 'NR', 2004, 27
FROM DUAL
UNION ALL
SELECT 'Beta', 'BB', 2006, 12
FROM DUAL
UNION ALL
SELECT 'Beta', 'BB+', 2001, 11
FROM DUAL),
matable1 AS
(SELECT nom, note, dt, rang,
COUNT (CASE
WHEN note = 'NR'
THEN 1
END) OVER (PARTITION BY nom) exist
FROM matable
WHERE dt BETWEEN 2001 AND 2006)
SELECT nom, note, dt, rang, exist
FROM matable1
WHERE exist = 0
NOM NOT DT RANG EXIST
----- --- ---------- ---------- ----------
Alpha A+ 2004 5 0
Alpha AA 2006 3 0
2 rows selected. |
Partager