Mysq查询总数、合格数及合格率

/*
CREATE TABLE `new_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stat` int(11) DEFAULT '0',
`_time` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
*/

#stat=0代表无效
#stat=1代表成功
#stat=2代表失败

use lihcheng;
SELECT * FROM new_table;

#select count(stat=1) as allCount from new_table ; #不能正确统计stat=1，即统计的是总数

select count(stat=1 or null) as allCount from new_table ;#or null代表stat!=1则返回null，count对null不会做统计，所以统计正确
select count(stat=1 and _time='2019-04-20' or null) as allCount from new_table ;#统计stat=1并且时间是2019-04-20的记录

select _time,count(stat>=0 or null) as allCount,count(stat=1 or null) as okCount from new_table group by _time; #按日期统计总数和合格数
select _time,count(stat>=0 or null) as allCount,count(stat=1 or null) as okCount, (count(stat=1 or null)/count(stat>=0 or null) *100) as yeild from new_table group by _time; #按日期统计总数和合格数，并计算合格率
select _time,count(CASE WHEN stat>=0 THEN 1 ELSE NULL END) as allCount,count(CASE WHEN stat=1 THEN 1 ELSE NULL END) as okCount, (count(stat=1 or null)/count(stat>=0 or null) *100) as yeild from new_table group by _time; #按日期统计总数和合格数，并计算合格率

转载于:https://www.cnblogs.com/ITCheng2019/p/10755560.html