一、知识总结拓展

1、知识点总结

1）exists 和 not exists
exists：也就是exists（非空才是真）括号里的内容为“非空”则继续执行外层的查询，否则为假则整个查询结果为空。
not exists ：也就是not exists（空才是真）括号里的内容为空则外层的查询为真继续执行，否则为假则整个查询结果为空

2）last_value() 和 frist_value() 
我的解题里没用到这个窗口函数，只是看到讨论中有同学用到了，觉得属于我的知识点盲区就记录上了。既可以根据返回某个字段的最后一行或者第一行，也可以被用于查找最大值或者最小值。
  • last_value(字段名1) over (order by   rows between unbounded preceding and unbounded following) 根据排序返回字段名1最后一行，也可以加上partition by 一起使用
  • frist_value(字段名2) over (order by   rows between unbounded preceding and unbounded following) 根据排序返回字段名2第一行，也可以加上partition by 一起使用

二、题目解读与解题步骤拆分

1、题目解读

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

题目中隐藏的坑与难点
我只想吐槽牛客题目的阅读理解太难了，你家题目的阅读理解，就是最大的坑和难点。😒😒😒
“当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。”说来惭愧，这句话我理解了挺久。这句话的意思其实就是：
  • 当level=0 且 incomplete_cnt>2这种情况出现时是结果A，
  • 如果这种情况不出现时是结果B，结果A B 出现的场景是互斥的也就是指只会出现一个结果
  • 小数点要保留3位数，incomplete_cnt空值要用0表示。
那我只需要把结果A和B都分别列出来，再根据真是条件选择即可。

需求字段

uid 用户ID
incomplete_cnt    未完成试卷数
incomplete_rate  试卷未完成率

2、步骤拆分

看了好几个讨论，笼统来看是两种解题方法。
解法1：先计算出每个用户的试卷未完成数和试卷未完成率，再直接给出两个条件分别对应的结果A和B，因为是互斥条件，直接并联结果A和B即可。

解法2：分别给出两种情况下会出现的结果，再筛选出所有结果并去重。

三、步骤代码

下面整理了大家用到的两种解题方式。

解法1：直接关联两种可能结局


1）计算用户的答题数，完未完成数，以及未完成率。 
SELECT b.uid,level,COUNT(start_time) total_cnt, #每个用户答题数
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt, #每个用户未完成答题数
    ROUND((COUNT(start_time)-COUNT(submit_time)) /COUNT(start_time),3) incomplete_rate #每个用户的未完成率。结果保留3位小数。
FROM exam_record a RIGHT JOIN user_info b ON a.uid=b.uid
GROUP BY b.uid; #表就命名为t1

  • 从红线看这种情况肯定是存在的啦
2）结果A：当level=0 且存在 incomplete_cnt>2时，输出level=0的用户答题指标信息 
SELECT uid,incomplete_cnt,incomplete_rate
FROM t1
WHERE EXISTS (SELECT uid FROM t1 WHERE level='0' AND incomplete_cnt>2) #出现（直接用exists表示出现）level=0且存在incomplete_cnt>2时
AND level='0' #输出level=0的用户未完成数和未完成率
;

3）结果B：当情况不出现时，输出所有有作答用户的答题指标信息（cnt_1>0） 
SELECT uid,incomplete_cnt,incomplete_rate
FROM t1
WHERE NOT EXISTS(SELECT uid FROM t1 WHERE level='0' AND incomplete_cnt>2) #没出现（直接用not exists表示没出现）level=0且存在incomplete_cnt>2时
AND total_cnt>0 #输出level=0的用户未完成数和未完成率
;
因为没出现这个（括号里的内容）为假，则结果为空。
4）答案：结果按照 incomplete_rate 排序。 
WITH t1 AS(
SELECT b.uid,level,COUNT(start_time) total_cnt, #每个用户答题数
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt, #每个用户未完成答题数
    IFNULL(ROUND((COUNT(start_time)-COUNT(submit_time))/COUNT(start_time),3),0) incomplete_rate #求每个用户的未完成率。若为空则显示为0，结果保留3位小数。
FROM exam_record a RIGHT JOIN user_info b ON a.uid=b.uid
GROUP BY b.uid)# 命名为t1的子查询

SELECT uid,incomplete_cnt,incomplete_rate
FROM t1
WHERE EXISTS(SELECT uid FROM t1 WHERE level='0' AND incomplete_cnt>2) #出现level=0且存在incomplete_cnt>2时
AND level='0' #输出level=0的用户未完成数和未完成率
UNION ALL
SELECT uid,incomplete_cnt,incomplete_rate
FROM t1
WHERE NOT EXISTS(SELECT uid FROM t1 WHERE level='0' AND incomplete_cnt>2) #没出现level=0且存在incomplete_cnt>2时
AND total_cnt>0 #输出level=0的用户未完成数和未完成率
ORDER BY incomplete_rate
;

解法2：给出两结果

1）求出0级用户的各个数据 
#0级用户的相关数据
SELECT b.uid,level,COUNT(start_time) total_cnt, #每个用户答题数
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt, #每个用户未完成答题数
    IFNULL(ROUND((COUNT(start_time)-COUNT(submit_time))/COUNT(start_time),3),0) incomplete_rate #求每个用户的未完成率。若为空则显示为0，结果保留3位小数。
FROM exam_record a RIGHT JOIN user_info b ON a.uid=b.uid
WHERE level='0'
GROUP BY b.uid;#表就命名为t1

2）求出所有有答题用户的各个数据 
SELECT uid,COUNT(start_time) total_cnt, #每个用户答题数
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt, #每个用户未完成答题数
    IFNULL(ROUND((COUNT(start_time)-COUNT(submit_time))/COUNT(start_time),3),0) incomplete_rate #求每个用户的未完成率。若为空则显示为0，结果保留3位小数。
FROM exam_record 
GROUP BY uid;
#表就命名为t2
3）如果level=‘0’时，存在incomplete_cnt>2 则返回t1表中的uid，否则返回t2表的uid。 
if((select max(incomplete_cnt) from t1)>2,t1.uid,t2.uid) uid
4）如果level=‘0’时，存在incomplete_cnt>2 则返回t1表中的incomplete_cnt，否则返回t2表的incomplete_cnt。 
if((select max(incomplete_cnt) from t1)>2,t1.incomplete_cnt,t2.incomplete_cnt) incomplete_cnt
5）如果level=‘0’时，存在incomplete_cnt>2 则返回t1表中的incomplete_rate，否则返回t2表的incomplete_rate。
if((select max(incomplete_cnt) from t1)>2,t1.incomplete_rate,t2.incomplete_rate) incomplete_rate
6）最后用GROUP BY 去重 
WITH t1 AS
(SELECT b.uid,level,COUNT(start_time) total_cnt, #每个用户答题数
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt, #每个用户未完成答题数
    IFNULL(ROUND((COUNT(start_time)-COUNT(submit_time))/COUNT(start_time),3),0) incomplete_rate #求每个用户的未完成率。若为空则显示为0，结果保留3位小数。
FROM exam_record a RIGHT JOIN user_info b ON a.uid=b.uid
WHERE level='0'
GROUP BY b.uid
)
, 
t2 AS
(SELECT uid,COUNT(start_time) total_cnt, #每个用户答题数
    COUNT(start_time)-COUNT(submit_time) incomplete_cnt, #每个用户未完成答题数
    IFNULL(ROUND((COUNT(start_time)-COUNT(submit_time))/COUNT(start_time),3),0) incomplete_rate #求每个用户的未完成率。若为空则显示为0，结果保留3位小数。
FROM exam_record 
GROUP BY uid)

SELECT if((select max(incomplete_cnt) from t1)>2,t1.uid,t2.uid) uid,
	if((select max(incomplete_cnt) from t1)>2,t1.incomplete_cnt,t2.incomplete_cnt) incomplete_cnt,
	if((select max(incomplete_cnt) from t1)>2,t1.incomplete_rate,t2.incomplete_rate) incomplete_rate

from t1,t2
group by uid
order by incomplete_rate;