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

条件：2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5）
- 2021年用where
```
where YEAR(start_time) = 2021 
```
- 未完成试卷作答数：无submit_time或者无score。用if函数。
```
select sum(if(submit_time is null, 1, 0)) as incomplete_cnt
    , sum(if(submit_time is null, 0, 1)) as complete_cnt
```
有效用户：完成试卷作答数至少为1且未完成数小于5。用having做筛选。
```
having incomplete_cnt<5
and complete_cnt >= 1
```
未完成试卷作答数大于1：
```
having incomplete_cnt > 1
```
输出结果：
- detail列：作答完成的试卷的日期和类型。用concat()连接？看了大神的答案才知道可以有个group_concat()函数。
- group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])
- 通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
```
group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';')
```

整理之后得到：

select uid
        , 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'),':',tag) separator ';') as detail
from exam_record er join examination_info 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