SQL40 根据指定记录是否存在输出不同情况

题目主要信息：

• 筛选表中的数据，当有0级用户未完成试卷数大于2时输出每个0级用户的试卷未完成数和未完成率（保留3位小数）
• 若不存在这样的用户，则输出所有有作答记录的用户的这两个指标
• 结果按未完成率升序排序

问题拆分：

• 先找到所有用户各自的完成题数和做的总题数：
  • 要计算每位用户的完成题数和做题总数，需根据uid分组。知识点：group by
  • uid直接获取，每位用户做题总数使用count统计每组总数。count(*) as total_cnt
  • 对于分组的得分，用if判断如果是null记为1，如果不是记为0，再累加每组的判断结果。sum(if(score is null, 1, 0)) as incomplete_cnt 知识点：sum、if
  • 筛选出的结果记为all_user_table
• 再找到满足题目第一串条件的用户：
  • 用户信息和作答信息分布在两个表中，因此要uid将两个表连接。知识点：join...on...
  • 要统计每个用户未完成试卷的数量，需要对uid分组。知识点：group by
  • 筛选条件是用户等级等于0，且每组总共未完成数量大于等于2.having sum(if(score is null, 1, 0)) >= 2 知识点：where、having、sum、if
  • 筛选出的结果记为level_zero_table
• 在用户信息表右边以uid连接上all_user_table，然后再连接上level_zero_table的uid。知识点：left join
• 从连接后的表格中筛选出用户ID、未完成数（需要判断如果未完成数为null取0）、未完成率（根据未完成数和all_user_table中的每人做题总数计算，需要判断如果未完成数为null取0）。知识点：if、round
• 筛选条件是表level_zero_table中的用户ID不为空，且用户等级为0或者表level_zero_table中的用户ID为空但是表all_user_table中的用户ID不为空。where (level_zero_table.uid is not null and level = 0) or (level_zero_table.uid is null and all_user_table.uid is not null)
• 按照未完成率升序输出。知识点：order by asc

代码：

select u_i.uid,
       if(incomplete_cnt is null, 0, incomplete_cnt) as incomplete_cnt,
       round(if(incomplete_cnt is null, 0, incomplete_cnt / total_cnt), 3) as incomplete_rate
from user_info u_i left join(
    select uid,
           sum(if(score is null, 1, 0)) as incomplete_cnt,
           count(*) as total_cnt
    from exam_record
    group by uid 
) all_user_table
on u_i.uid = all_user_table.uid
left join(
    select e_r.uid as uid
    from exam_record e_r join user_info u_i
    on e_r.uid = u_i.uid
    where level = 0
    group by uid
    having sum(if(score is null, 1, 0)) >= 2
) level_zero_table
on 1 = 1
where (level_zero_table.uid is not null and level = 0)
or (level_zero_table.uid is null and all_user_table.uid is not null)
order by incomplete_rate asc