题解 | #根据指定记录是否存在输出不同情况#

题目：

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

难点：

这道题最大的难点就在，它看起来是“如果A，则B；如果不是A，则C”的一个条件分支，在处理这个条件分支的实际过程中，会发现“是A”和“不是A”之间的界限划分是很需要对关系逻辑的良好运用
我会给出两个比较好理解的解决方法，一个是比较常规的，一个是在WHERE子句中使用CASE WHEN的 —— 后者我会直接放置@ZHMM 的答案，写得非常好，大家可以多参考

解题方法

方法 1：需要对CASE WHEN、开窗函数、WHERE的良好理解

步骤 1：先找出每一位用户的试卷作答记录，并计算出他们的作答次数、未完成次数、未完成率等

这一步很简单了，就是两个表联结，再使用CASE WHEN来计算作答次数、未完成次数、未完成率等，直接上代码：

SELECT ui.uid, ui.level,
     /* 当exam_id不为空时，才代表该用户有作答记录，SUM函数计算总作答次数 */
     SUM(CASE WHEN er.exam_id IS NOT NULL THEN 1 ELSE 0 END) AS start_cnt,
     /* 由于是user_info左联结exam_record，因此需要score为空且start_time不为空时，才认定该用户有作答但未提交；
        否则，会导致没有过作答记录、分数为NULL的用户也会被认为作答过但未提交；SUM函数计算未完成次数 */
     SUM(CASE WHEN er.score IS NULL AND start_time IS NOT NULL THEN 1 ELSE 0 END) AS incomplete_cnt,
     /* 同样地，先看最底层的两个SUM函数，其实就是未完成次数与作答次数相除，显得出未完成率；
        由于存在作答次数为0，即分母为0导致结果为NULL的情况，所以还需要使用IFNULL来将NULL转化为0；
        最后则是ROUND函数来四舍五入，取3个小数位 */
     ROUND(IFNULL(SUM(CASE WHEN er.score IS NULL AND start_time IS NOT NULL THEN 1 ELSE 0 END) / SUM(CASE WHEN ui.uid IS NOT NULL AND er.exam_id IS NOT NULL THEN 1 ELSE 0 END), 0), 3) AS incomplete_rate
FROM user_info AS ui LEFT OUTER JOIN exam_record AS er
ON ui.uid = er.uid
GROUP BY ui.uid, ui.level

步骤 2：使用开窗函数，来判断是否存在“0级用户未完成试卷数大于2”的情况

如果存在该情况，则为对应记录行打上“1”这个标签，否则，打上“0”标签
我使用的是SUM函数与开窗函数的结合，所得出的新字段，实际上代表：“未完成试卷数大于2的0级用户有多少个”

最终，由于使用的是是OVER ()，因此，所有记录行都会被打上同一个标签：“0个”或者“1及1以上”这两种情况

SELECT t1.*,
     SUM(CASE WHEN incomplete_cnt > 2 AND level = 0 THEN 1 ELSE 0 END) OVER () AS judge
FROM
      (SELECT ui.uid,
              ui.level,
              SUM(CASE WHEN er.exam_id IS NOT NULL THEN 1 ELSE 0 END) AS start_cnt,
              SUM(CASE WHEN er.score IS NULL AND start_time IS NOT NULL THEN 1 ELSE 0 END) AS incomplete_cnt,
              ROUND(IFNULL(SUM(CASE WHEN er.score IS NULL AND start_time IS NOT NULL THEN 1 ELSE 0 END) / SUM(CASE WHEN ui.uid IS NOT NULL AND er.exam_id IS NOT NULL THEN 1 ELSE 0 END), 0), 3) AS incomplete_rate
       FROM user_info AS ui LEFT OUTER JOIN exam_record AS er
       ON ui.uid = er.uid
       GROUP BY ui.uid, ui.level) AS t1

步骤 3：关键一步，亦是最终一步，根据“是否存在未完成试卷数大于2的0级用户”来选择输出哪些用户的记录

这里有一个非常关键又简单的数学逻辑需要意识到：“未完成试卷数大于2的0级用户”只有“存在”和“不存在”两种情况，即，对应地，judge字段只会有“0”或者“大于等于1”两个情况
因此，该两个情况是互斥的，其中一个存在，则另外一个一定不存在
那么，在选择SELECT哪几个用户的结果，实质上就可以用过WHERE来进行筛选了：当judge为0（即不存在该种用户时），则再选择有过作答记录（即start_cnt不为0）的用户；当judge大于等于1时，即选择等级为0（即level为0）的用户；这两个筛选条件，可以直接使用OR来连接，一并在WHERE子句中使用 —— 还是那句话，只要judge为0，则一定不会再select出judge为1的情况，反之亦然，所以这个OR实际上也只会返还一个情况下的结果

SELECT uid, incomplete_cnt, incomplete_rate
FROM
   (
    SELECT t1.*,
            SUM(CASE WHEN incomplete_cnt > 2 AND level = 0 THEN 1 ELSE 0 END) OVER () AS judge
    FROM
        (SELECT ui.uid,
                ui.level,
                SUM(CASE WHEN er.exam_id IS NOT NULL THEN 1 ELSE 0 END) AS start_cnt,
                SUM(CASE WHEN er.score IS NULL AND start_time IS NOT NULL THEN 1 ELSE 0 END) AS incomplete_cnt,
                ROUND(IFNULL(SUM(CASE WHEN er.score IS NULL AND start_time IS NOT NULL THEN 1 ELSE 0 END) / SUM(CASE WHEN ui.uid IS NOT NULL AND er.exam_id IS NOT NULL THEN 1 ELSE 0 END), 0), 3) AS incomplete_rate
         FROM user_info AS ui LEFT OUTER JOIN exam_record AS er
         ON ui.uid = er.uid
         GROUP BY ui.uid, ui.level) AS t1
   ) AS t2
WHERE (judge >= 1 AND level = 0) OR (judge = 0 AND start_cnt > 0)
ORDER BY incomplete_rate ASC;

方法 2：更加快速，需要对在WHERE中使用CASE WHEN有理解

这部分我直接上答案，后续有时间给各位解析一下

SELECT
  ui.uid,
  SUM(IF(start_time IS NOT NULL AND score IS NULL,1,0)) AS incomplete_cnt,#3.试卷未完成数
  ROUND(AVG(IF(start_time IS NOT NULL AND score IS NULL,1,0)),3) AS incomplete_rate#4.未完成率
FROM user_info ui
LEFT JOIN exam_record USING(uid)
WHERE CASE WHEN (#1.当有任意一个0级用户未完成试卷数大于2时
  SELECT MAX(lv0_incom_cnt) FROM(
      SELECT SUM(IF(score IS NULL,1,0)) AS lv0_incom_cnt
      FROM user_info
      JOIN exam_record USING(uid)
      WHERE level=0
      GROUP BY uid
      )table1
  )>2
  THEN uid IN(#1.1找出每个0级用户
      SELECT uid
      FROM user_info
      WHERE level=0
  )
  ELSE uid IN(#2.若不存在这样的用户，找出有作答记录的用户
      SELECT DISTINCT uid
      FROM exam_record
  )
  END
GROUP BY ui.uid
ORDER BY incomplete_rate#5.结果按未完成率升序排序