为此,您可以使用CASE WHEN语句。让我们首先创建一个表-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)