题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
http://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
SELECT a.uid,
SUM(CASE WHEN a.submit_time IS NULL THEN 1 ELSE 0 END) incomplete_cnt,
SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 ELSE 0 END) complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time,'%Y-%m-%d'),':',b.tag)
order by a.start_time separator';') detail
FROM exam_record a
LEFT JOIN examination_info b
on a.exam_id=b.exam_id
WHERE YEAR(a.start_time)=2021
GROUP BY a.uid
HAVING incomplete_cnt>1 AND incomplete_cnt<5 AND complete_cnt>=1
SUM(CASE WHEN a.submit_time IS NULL THEN 1 ELSE 0 END) incomplete_cnt,
SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 ELSE 0 END) complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time,'%Y-%m-%d'),':',b.tag)
order by a.start_time separator';') detail
FROM exam_record a
LEFT JOIN examination_info b
on a.exam_id=b.exam_id
WHERE YEAR(a.start_time)=2021
GROUP BY a.uid
HAVING incomplete_cnt>1 AND incomplete_cnt<5 AND complete_cnt>=1
ORDER BY incomplete_cnt DESC;
注意：CASE WHEN 这里不能用SCORE,只能用submit_time。因为竟然第一项，有成绩，没有提交时间。详情看下表。
我真的无语了。这傻鸟逻辑。整了半天。
投递大疆等公司10个岗位 >