背景
现在有一个需求,就是用一条sql语句如何查出试卷表(exam_user)中的,已批改人数,已答未批改人数,未答人数。
id | exam_id | user_id | id_done | is_review |
---|---|---|---|---|
1 | 1 | 1 | 0 | 0 |
2 | 1 | 2 | 1 | 0 |
3 | 1 | 3 | 1 | 0 |
4 | 1 | 4 | 1 | 1 |
解决
编写sql
sqlSELECT SUM(IF((eu.`is_done` = 1 AND eu.`is_review` = 1),1,0)) AS corrected,SUM(IF((eu.`is_done` = 1 AND eu.`is_review` = 0),1,0)) AS uncorrected,SUM(IF((eu.`is_done` = 0 ),1,0)) AS noAnswered FROM exam_user eu WHERE eu.`exam_id` = 1;
执行结果
corrected | uncorrected | noAnswered |
---|---|---|
1 | 2 | 1 |
解析:
SUM(IF((eu.is_done
= 1 AND eu.is_review
= 1),1,0)) ,意思就是当 eu.is_done
= 1 AND eu.is_review
= 1条件成立时,SUM加1,不成立时加0。