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 t
(
nom VARCHAR(20),
prenom VARCHAR(20),
coup_1 INT,
coup_2 INT,
coup_3 INT,
coup_4 INT,
passe1 INT,
passe2 INT,
total INT
)
GO
INSERT INTO t VALUES ('toto','toto',1, 11, 2, 11, 12, 13, 25)
INSERT INTO t VALUES ('tota','tota',11, 11, 2, 11, 22, 13, 35)
INSERT INTO t VALUES ('toti','toti',1, 11, 7, 5, 12, 13, 25)
GO
SELECT
nom,
prenom,
CASE coup_1 WHEN 11 THEN 1 ELSE 0 END +
CASE coup_2 WHEN 11 THEN 1 ELSE 0 END +
CASE coup_3 WHEN 11 THEN 1 ELSE 0 END +
CASE coup_4 WHEN 11 THEN 1 ELSE 0 END AS nb_11,
total
FROM t
ORDER BY total DESC, nb_11 DESC |
Partager