在 MySQL 中使用 CASE WHEN 语句执行计数?
mysqlmysqli database更新于 2024/1/9 1:55:00
为此，您可以使用 CASE WHEN 语句。让我们首先创建一个表 −
mysql> create table DemoTable1910 ( FirstName varchar(20), Marks int ); Query OK, 0 rows affected (0.00 sec)
使用 insert 命令在表中插入一些记录 −
mysql> insert into DemoTable1910 values('Chris',45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('David',85);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('Chris',55);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('Chris',98);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('David',98);
Query OK, 1 row affected (0.00 sec)
使用 select 语句显示表中的所有记录 −
mysql> select * from DemoTable1910;
这将产生以下输出 −
+-----------+-------+ | FirstName | Marks | +-----------+-------+ | Chris | 45 | | David | 85 | | Chris | 55 | | Chris | 98 | | David | 98 | +-----------+-------+ 5 rows in set (0.00 sec)
这是有效计算列值的查询 −
mysql> select FirstName, sum(case when Marks=45 then 1 else 0 end), sum(case when Marks=98 then 1 else 0 end) from DemoTable1910 group by FirstName;
这将产生以下输出 −
+-----------+-------------------------------------------+-------------------------------------------+ | FirstName | sum(case when Marks=45 then 1 else 0 end) | sum(case when Marks=98 then 1 else 0 end) | +-----------+-------------------------------------------+-------------------------------------------+ | Chris | 1 | 1 | | David | 0 | 1 | +-----------+-------------------------------------------+-------------------------------------------+ 2 rows in set (0.00 sec)
