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
| CREATE TABLE CONCURRENT (Numero INT, NOM VARCHAR(16))
INSERT INTO CONCURRENT VALUES (1, 'A')
INSERT INTO CONCURRENT VALUES (2, 'B')
INSERT INTO CONCURRENT VALUES (3, 'C')
INSERT INTO CONCURRENT VALUES (4, 'D')
INSERT INTO CONCURRENT VALUES (5, 'E')
CREATE TABLE RESULTAT (N_CONC INT, TEMPS DECIMAL(16,2))
INSERT INTO RESULTAT VALUES (1, 3.25)
INSERT INTO RESULTAT VALUES (2, 3.15)
INSERT INTO RESULTAT VALUES (3, 3.09)
INSERT INTO RESULTAT VALUES (4, 3.08)
INSERT INTO RESULTAT VALUES (5, 3.11)
-- en SQL 2 :
SELECT NOM, RANG, (SELECT MIN(TEMPS)
FROM RESULTAT) - (SELECT TEMPS
FROM RESULTAT AS RR
WHERE RR.N_CONC = C.Numero) AS ECART
FROM (SELECT R1.N_CONC, COUNT(*) AS RANG
FROM RESULTAT AS R1
INNER JOIN RESULTAT AS R2
ON R1.TEMPS >= R2.TEMPS
GROUP BY R1.N_CONC) AS T
INNER JOIN CONCURRENT AS C
ON T.N_CONC = C.Numero
ORDER BY RANG |
Partager