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

未完成试卷数大于1的有效用户

https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286

题目：

请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），
输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，结果按未完成试卷数量由多到少排序。

注意，题目还有一点要求其实没讲清楚，但在结果中有要求到：在字段“detail”中的“{日期:tag}集合”，也需要按照日期升序来排序：比如，必须要是“2021-07-02:SQL;2021-07-05:SQL;2021-09-01:算法;2021-09-02:SQL;2021-09-05:SQL”
另外，还有一个大雷点是，本题目存在“submit_time”为NULL但依旧存在score有数的情况，因此，在计算未完成数时，我会使用submit_time来做

所用新函数：GROUP_CONCAT()

参考易百教程，GROUP_CONCAT()函数将组中的字符串连接成为具有各种选项的单个字符串。下面说明了GROUP_CONCAT()函数的语法：
```
GROUP_CONCAT(DISTINCT expression
             ORDER BY expression
             SEPARATOR sep);
```
比如说，我想要根据字段“submit_time”中多行的值都合并为一个值，每个值间使用分号“;”间隔开，并且按照submit_time的时间升序来合并，那我就可以写成：
```
GROUP_CONCAT(DISTINCT submit_time
             ORDER BY submit_time ASC
             SEPARATOR ";");
```

解题思路

步骤 1：从简单的先开始，先计算每个用户未完成数和完成数，并找出有效用户

老生常谈了，不赘述，直接上代码：

SELECT er.uid,
      COUNT(CASE WHEN er.submit_time IS NULL THEN 1 ELSE NULL END) AS incomplete_cnt,
      COUNT(er.submit_time) AS complete_cnt
FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
WHERE YEAR(er.start_time) = 2021
GROUP BY er.uid
/* 找出未完成试卷作答数大于1的有效用户 */
HAVING complete_cnt >= 1 AND (incomplete_cnt BETWEEN 2 AND 4)

步骤 2：针对作答过的试卷集合（即detail字段），需要先整合出“{日期:tag}”这一新字段，再根据UID分组去合并该字段下的不同行

注意，这里还需要DISTINCT去重，即，若存在同一天做了同一个类型试卷多次，则只需要取一个即可；比如说“2021-09-05:算法;2021-09-05:算法” --> “2021-09-05:算法”
另外，合并的时候，也需要按照日期升序

先看第一小步，整合出新字段“{日期:tag}”：

SELECT er.uid,
       /* 整合新字段，只需要使用CONCAT函数，让start_time和 */
       CONCAT(DATE(er.start_time), ":", ei.tag) 
FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
WHERE YEAR(er.start_time) = 2021

第二小步，要根据UID分组之后，把UID对应的“{日期:tag}”行都再次整合起来，这里就需要用到GROUP_CONCT()函数：

SELECT er.uid,
       /* 对“{日期:tag}”去重，并按照日期升序（这里直接按照新字段升序即可，因为日期在前半部分）；
           最后再用分号“;”把它们分分隔开 */
       GROUP_CONCAT(DISTINCT CONCAT(DATE(er.start_time), ":", ei.tag) 
                    ORDER BY CONCAT(DATE(er.start_time), ":", ei.tag) ASC
                    SEPARATOR ";") AS detail
FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
WHERE YEAR(er.start_time) = 2021
GROUP BY er.uid

步骤 3：可以看到步骤2和1其实是可以合并到一起的，最终得出来的就是题目所需

不知是否有同学会有疑问说，步骤1明明和步骤2还有个不同：步骤1中会用HAVING这一筛选条件 —— 其实该条件并不影响步骤2的detail产出，因为HAVING是在得出分组结果之后，基于分组结果再筛选/行数删减的；即，步骤2的detail字段是在分组得出的结果，而HAVING是无法再对其造成影响的，只是删减了原有分组结果的行数

SELECT er.uid,
      COUNT(CASE WHEN er.submit_time IS NULL THEN 1 ELSE NULL END) AS incomplete_cnt,
      COUNT(er.submit_time) AS complete_cnt,
      GROUP_CONCAT(DISTINCT CONCAT(DATE(er.start_time), ":", ei.tag) 
                   ORDER BY CONCAT(DATE(er.start_time), ":", ei.tag) ASC
                   SEPARATOR ";") AS detail
FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
WHERE YEAR(er.start_time) = 2021
GROUP BY er.uid
HAVING complete_cnt >= 1 AND (incomplete_cnt BETWEEN 2 AND 4)
ORDER BY incomplete_cnt DESC;