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
| WITH service AS
(
SELECT 's1' Numserv ,'Comptabilité' libserv FROM dual union
SELECT 's2' ,'Ressources Humaines' FROM dual union
SELECT 's3' ,'Production' FROM dual
), EMPLOYE AS
(SELECT 'e1'Numemp,'Cantou' nom,'Bernard' prenom,'f1' numfonct,'s1' numserv FROM dual union
SELECT 'e2', 'Bleuet' , 'Dominique','f2','s2' FROM dual union
SELECT 'e3', 'Blanc' ,'Maxime','f3','s2' FROM dual union
SELECT 'e4', 'AAAA' ,'aaaa','f3','s1' FROM dual union
SELECT 'e9', 'BBBB' ,'bbbb','f4','s1' FROM dual union
SELECT 'e5', 'CCCC' ,'cccc','f2','s1' FROM dual union
SELECT 'e6', 'DDDD' ,'dddd','f3','s1' FROM dual union
SELECT 'e10', 'Deliat' , 'Justine','f4','s3' FROM dual union
SELECT 'e7', 'EEEE' , 'eeee','f1','s3' FROM dual union
SELECT 'e8', 'FFFF' , 'ffff','f5','s2' FROM dual
)
,FONCTION AS
(
SELECT 'f1' Numfonct, 'LF1' libfonct, 1000 salaire FROM dual union
SELECT 'f2' Numfonct, 'LF2' libfonct, 5000 salaire FROM dual union
SELECT 'f3' Numfonct, 'LF3' libfonct, 4000 salaire FROM dual union
SELECT 'f4' Numfonct, 'LF4' libfonct, 2000 salaire FROM dual union
SELECT 'f5' Numfonct, 'LF5' libfonct, 7000 salaire FROM dual union
SELECT 'f6' Numfonct, 'LF6' libfonct, 600 salaire FROM dual
)
SELECT sal, ser, nomm, pren
FROM
(
SELECT salaire sal , service.numserv ser , employe.nom nomm , employe.prenom pren,row_number() over (partition BY service.numserv ORDER BY salaire DESC, employe.nom ASC, employe.prenom ASC) AS rang
FROM fonction
INNER JOIN employe ON employe.numfonct=fonction.numfonct
INNER JOIN service ON service.numserv=employe.numserv
) ordered
WHERE ordered.rang = 1 |
