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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
CREATE TABLE `test`
( `id` INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY,
`pseudo` VARCHAR(255) NOT NULL,
`point` INTEGER UNSIGNED NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `test` (`pseudo`,`point`) VALUES
('un',15),('deux',35),('trois',12),('un',8),('trois',25),('deux',35),('trois',2)
--------------
--------------
select * from `test`
--------------
+----+--------+-------+
| id | pseudo | point |
+----+--------+-------+
| 1 | un | 15 |
| 2 | deux | 35 |
| 3 | trois | 12 |
| 4 | un | 8 |
| 5 | trois | 25 |
| 6 | deux | 35 |
| 7 | trois | 2 |
+----+--------+-------+
--------------
create view `vue` as
select pseudo,
sum(point) as point
from `test`
group by pseudo
order by point desc
--------------
--------------
select * from `vue`
--------------
+--------+-------+
| pseudo | point |
+--------+-------+
| deux | 70 |
| trois | 39 |
| un | 23 |
+--------+-------+
--------------
select t1.pseudo,
t2.rang,
t1.point,
t3.nbre
from `vue` as t1
inner join ( select t4.pseudo,
count(t5.pseudo)+1 as rang
from `vue` as t4
left outer join `vue` as t5
on t5.point > t4.point
group by t4.pseudo
) as t2
on t2.pseudo = t1.pseudo
inner join ( select count(distinct pseudo) as nbre
from `test`
) as t3
where t1.pseudo = 'trois'
--------------
+--------+------+-------+------+
| pseudo | rang | point | nbre |
+--------+------+-------+------+
| trois | 2 | 39 | 3 |
+--------+------+-------+------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager