mysql及格率公式_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; #按日期统计总数和合格数，并计算合格率