MySQL 查询获取值的数量并在新列中按降序显示该数量\
mysqlmysqli database更新于 2025/9/16 3:56:17
使用 ORDER BY 和 DESC 按降序排列。要计算值的数量，请使用 COUNT()。例如，如果名称"John"在列中出现三次，则单独的列将显示数量 3，这样所有计数值将使用 ORDER BY DESC 按降序排列。
首先我们创建一个表 −
mysql> create table DemoTable -> ( -> EmployeeName varchar(100) -> ); Query OK, 0 rows affected (0.85 sec)
使用 insert 命令在表中插入一些记录 −
mysql> insert into DemoTable values('Sam');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('David');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('David');
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Sam');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Sam');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('David');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('David');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.14 sec)
使用 select 语句显示表中的所有记录 −
mysql> select *from DemoTable;
输出
+--------------+ | EmployeeName | +--------------+ | Sam | | David | | David | | Chris | | Robert | | Chris | | Sam | | Sam | | David | | Robert | | David | | Chris | +--------------+ 12 rows in set (0.00 sec)
以下查询用于获取值的数量并按降序显示在新列中 −
mysql> select EmployeeName,count(EmployeeName) as Total from DemoTable -> group by EmployeeName -> order by Total DESC;
输出
+--------------+-------+ | EmployeeName | Total | +--------------+-------+ | David | 4 | | Chris | 3 | | Sam | 3 | | Robert | 2 | +--------------+-------+ 4 rows in set (0.00 sec)
相关文章
有用资源
mysql 参考教程 - 该教程包含有关 mysql 的更多信息:https://www.cainiaomax.com/mysql/
