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