题解 | #未完成试卷数大于1的有效用户#

select uid, 
COUNT(start_time) - COUNT(submit_time) AS incomplete_cnt,
COUNT(submit_time) AS complete_cnt,
#SUM(if(submit_time is NULL,1,0)) AS incomplete_cnt,
#SUM(if(submit_time is NULL,0,1)) AS complete_cnt, #这两个也可以
GROUP_CONCAT(distinct CONCAT(date_format(start_time,'%Y-%m-%d'),':',ei.tag) ORDER BY start_time separator ';') AS detail
FROM exam_record AS er
JOIN examination_info AS ei ON er.exam_id = ei.exam_id
WHERE YEAR(start_time) = '2021' 
GROUP BY uid
HAVING incomplete_cnt > 1
AND  incomplete_cnt < 5
AND complete_cnt >= 1
ORDER BY incomplete_cnt DESC

使用 GROUP_CONCAT() 函数。这个函数可以将每个组中的多个值连接成一个单独的字符串，并且你可以指定连接的分隔符。

GROUP_CONCAT(distinct CONCAT(date_format(start_time,'%Y-%m-%d'),':',ei.tag) ORDER BY start_time separator ';')

GROUP_CONCAT 对每个组的字符串进行连接，根据start_time进行排序，指定分隔符 separator ';'

CONCAT 进行字符串的拼接：CONCAT(date_format(start_time,'%Y-%m-%d'),':',ei.tag)

concat可以拼接多个字符串