在MySQL中使用CASE WHEN语句执行计数？
为此，您可以使用CASEWHEN语句。让我们首先创建一个表-
create table DemoTable1910 ( FirstName varchar(20), Marks int );
使用插入命令在表中插入一些记录-
insert into DemoTable1910 values('Chris',45);
insert into DemoTable1910 values('David',85);
insert into DemoTable1910 values('Chris',55);
insert into DemoTable1910 values('Chris',98);
insert into DemoTable1910 values('David',98);使用select语句显示表中的所有记录-
select * from DemoTable1910;
这将产生以下输出-
+-----------+-------+ | FirstName | Marks | +-----------+-------+ | Chris | 45 | | David | 85 | | Chris | 55 | | Chris | 98 | | David | 98 | +-----------+-------+ 5 rows in set (0.00 sec)
这是有效计数列值的查询-
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)