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
|
CREATE TABLE T_TEST_LANGUE_TLG
( TLG_MOIS INT ,
TLG_LANGUE VARCHAR (2),
TLG_NOMBRE INT );
SELECT * FROM T_TEST_LANGUE_TLG
ORDER BY 1, 3 DESC
INSERT INTO T_TEST_LANGUE_TLG VALUES (1, 'ES', 3);
INSERT INTO T_TEST_LANGUE_TLG VALUES (2, 'ES', 5);
INSERT INTO T_TEST_LANGUE_TLG VALUES (3, 'ES', 1);
INSERT INTO T_TEST_LANGUE_TLG VALUES (1, 'FR', 4);
INSERT INTO T_TEST_LANGUE_TLG VALUES (2, 'FR', 8);
INSERT INTO T_TEST_LANGUE_TLG VALUES (3, 'FR', 3);
INSERT INTO T_TEST_LANGUE_TLG VALUES (1, 'BE', 7);
INSERT INTO T_TEST_LANGUE_TLG VALUES (2, 'BE', 3);
INSERT INTO T_TEST_LANGUE_TLG VALUES (3, 'BE', 6);
INSERT INTO T_TEST_LANGUE_TLG VALUES (1, 'DK', 3);
INSERT INTO T_TEST_LANGUE_TLG VALUES (2, 'DK', 6);
INSERT INTO T_TEST_LANGUE_TLG VALUES (3, 'DK', 2);
INSERT INTO T_TEST_LANGUE_TLG VALUES (1, 'GB', 1);
INSERT INTO T_TEST_LANGUE_TLG VALUES (2, 'GB', 7);
INSERT INTO T_TEST_LANGUE_TLG VALUES (3, 'GB', 8);
INSERT INTO T_TEST_LANGUE_TLG VALUES (1, 'RU', 1);
INSERT INTO T_TEST_LANGUE_TLG VALUES (2, 'RU', 2);
INSERT INTO T_TEST_LANGUE_TLG VALUES (3, 'RU', 1);
SELECT T1.TLG_MOIS, T1.TLG_LANGUE, T1.TLG_NOMBRE,
COUNT(ALL T2.TLG_NOMBRE) AS CLASSEMENT
FROM T_TEST_LANGUE_TLG T1
INNER JOIN T_TEST_LANGUE_TLG T2
ON T1.TLG_MOIS = T2.TLG_MOIS
AND T1.TLG_NOMBRE <= T2.TLG_NOMBRE
GROUP BY T1.TLG_MOIS, T1.TLG_LANGUE, T1.TLG_NOMBRE
HAVING COUNT(ALL T2.TLG_NOMBRE) <= 5
ORDER BY 1, 4 |
Partager