在MySQL中使用条件计算？

要进行计数，请使用聚合函数SUM()并根据条件进行计数，您需要使用WHERE设置条件。让我们首先创建一个表-

create table DemoTable1515 -> ( -> ClientId varchar(10), -> ClientName varchar(20) -> );

使用插入命令在表中插入一些记录-

insert into DemoTable1515 values(CLI-101,Chris); insert into DemoTable1515 values(CLI-110,David); insert into DemoTable1515 values(CLI-101,Bob); insert into DemoTable1515 values(CLI-101,Sam); insert into DemoTable1515 values(CLI-101,Sam); insert into DemoTable1515 values(CLI-101,Mike); insert into DemoTable1515 values(CLI-130,Mike); insert into DemoTable1515 values(CLI-101,Chris); insert into DemoTable1515 values(CLI-101,Sam);

使用select语句显示表中的所有记录-

select * from DemoTable1515;

这将产生以下输出-

+----------+------------+ | ClientId | ClientName | +----------+------------+ | CLI-101 | Chris | | CLI-110 | David | | CLI-101 | Bob | | CLI-101 | Sam | | CLI-101 | Sam | | CLI-101 | Mike | | CLI-130 | Mike | | CLI-101 | Chris | | CLI-101 | Sam | +----------+------------+ 9 rows in set (0.00 sec)

这是要计算的查询条件-

select ClientId,sum(ClientName=Sam) as NameOn101Id,count(*) as TotalNameOn101 from DemoTable1515 -> where ClientId=CLI-101;

这将产生以下输出-

+----------+-------------+----------------+ | ClientId | NameOn101Id | TotalNameOn101 | +----------+-------------+----------------+ | CLI-101 | 3 | 7 | +----------+-------------+----------------+ 1 row in set (0.00 sec)