题解 | #统计作答次数#
统计作答次数
http://www.nowcoder.com/practice/45a87639110841b6950ef6a12d20175f
明确题意：
统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。
问题拆解：
- 本题主要是考察知识点：count、case when、distinct
- total_pv = count(*)
-
complete_pv = count(where score is not null)
- complete_exam_cnt = count(distinct exam_id when score is not null)
代码实现：
select count(*) as total_pv, count(case when score is not null then 1 else null end) as complete_pv, count(distinct case when score is not null then exam_id else null end) as complete_exam_cnt from exam_record ;
注意：
- 如果写成如下，结果是错误的，0也会计入count，null此时不会计入count！！！
count(case when score is not null then 1 else 0 end) as complete_pv,
- 如果写成如下，结果是正确的！！！
sum(case when score is not null then 1 else 0 end) as complete_pv, 或者 sum(case when score is not null then 1 else null end) as complete_pv,一般求记录总数时都是用count(....null...)。
不足之处，欢迎指正。
投递蔚来等公司10个岗位 >
查看12道真题和解析