在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)