题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
SELECT uid, SUM(incomplete) AS incomplete_cnt, SUM(complete) AS complete_cnt, GROUP_CONCAT(DISTINCT detail SEPARATOR ';') AS detail FROM ( SELECT uid, CONCAT (DATE (start_time), ':', tag) AS detail, if (submit_time is not null, 1, 0) as complete, if (submit_time is null, 1, 0) as incomplete FROM exam_record er JOIN examination_info ei ON er.exam_id = ei.exam_id WHERE YEAR (start_time) = 2021 ) as new_table GROUP BY uid HAVING incomplete_cnt>1 AND incomplete_cnt<5 AND complete_cnt>=1 ORDER BY incomplete_cnt DESC;
GROUP_CONCAT用法