MySQL 查询查找前两个最高分数
mysqlmysqli database
为此，使用聚合函数 MAX()。让我们首先创建一个表 −
mysql> create table DemoTable1383 -> ( -> Id int, -> PlayerScore int -> ); Query OK, 0 rows affected (0.90 sec)
使用 insert 命令在表中插入一些记录 −
mysql> insert into DemoTable1383 values(200,78); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1383 values(200,89); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1383 values(200,89); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1383 values(200,87); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable1383 values(203,97); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1383 values(200,57); Query OK, 1 row affected (0.17 sec)
使用 select 语句显示表中的所有记录 −
mysql> select * from DemoTable1383;
这将产生以下输出 −
+------+-------------+ | Id | PlayerScore | +------+-------------+ | 200 | 78 | | 200 | 89 | | 200 | 89 | | 200 | 87 | | 203 | 97 | | 200 | 57 | +------+-------------+ 6 rows in set (0.00 sec)
以下是查找最高分数并显示不同值的查询 −
mysql> select Id,max(PlayerScore) from DemoTable1383 group by Id order by max(PlayerScore) DESC;
这将产生以下输出 −
+------+------------------+ | Id | max(PlayerScore) | +------+------------------+ | 203 | 97 | | 200 | 89 | +------+------------------+ 2 rows in set (0.00 sec)
