通过代码1

with t as (
SELECT
    u_i.uid,
    level,
    tag,
    if(u_i.uid not in (SELECT uid FROM exam_record),0,incomplete_cnt) incomplete_cnt,
    round(if(u_i.uid not in (SELECT uid FROM exam_record),0,incomplete_rate),3) incomplete_rate
FROM
    user_info u_i
LEFT JOIN(
SELECT
    uid,
    sum(if(score is NULL,1,0)) incomplete_cnt,
    sum(if(score is NULL,1,0)) / count(*) incomplete_rate,
    '1' tag
FROM
    exam_record
GROUP BY
    uid) e
ON
    u_i.uid = e.uid
GROUP BY
    u_i.uid
)


SELECT 
    uid,
    incomplete_cnt,
    incomplete_rate
FROM
    t 
where EXISTS(
    select 
     uid 
 from 
     t 
 where 
     level=0 and incomplete_cnt>2
) and level = 0

union all

SELECT 
    uid,
    incomplete_cnt,
    incomplete_rate
FROM
    t 
where not EXISTS(
    select 
     uid 
 from 
     t 
 where 
     level=0 and incomplete_cnt>2
) and tag = '1'
order by 
    incomplete_rate

思路

先上题目要求：筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。另外未作答过试卷，未完成率默认填0，保留3位小数后是0.000

我们先干嘛呢？我觉得首先得把exam_record中的相应指标查并标记不然到时候我们怎么知道那些是有作答记录的用户呢？未完成数目和未完成率为0显然不能证明他没有作答记录

1.有作答记录筛选

SELECT
    uid,
    sum(if(score is NULL,1,0)) incomplete_cnt,
    sum(if(score is NULL,1,0)) / count(*) incomplete_rate,
    '1' tag
FROM
    exam_record
GROUP BY
    uid

给了一个1作为tag记录

2.无作答记录并入表一

SELECT
    u_i.uid,
    level,
    tag,
    if(u_i.uid not in (SELECT uid FROM exam_record),0,incomplete_cnt) incomplete_cnt,
    round(if(u_i.uid not in (SELECT uid FROM exam_record),0,incomplete_rate),3) incomplete_rate
FROM
    user_info u_i
LEFT JOIN(
GROUP BY(1.代码) e
ON
    u_i.uid = e.uid
GROUP BY
    u_i.uid

这里我用了not in 手动置零，现在这个表里tag为1就是有作答记录的人。

3.当……这样，否则那样

SELECT 
    uid,
    incomplete_cnt,
    incomplete_rate
FROM
    t 
where EXISTS(
    select 
     uid 
 from 
     t 
 where 
     level=0 and incomplete_cnt>2
) and level = 0
union all
SELECT 
    uid,
    incomplete_cnt,
    incomplete_rate
FROM
    t 
where not EXISTS(
    select 
     uid 
 from 
     t 
 where 
     level=0 and incomplete_cnt>2
) and tag = '1'
order by 
    incomplete_rate

因为我没看过书，就直接做题遇到不会的再学习，所以这里就是对我来说的新知识：where exists

EXISTS执行顺序：

1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A

2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id

3、如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果

对这个题就是如果level=0 and incomplete_cnt>2 那就筛选level = 0的输出

这里没有否则，就是如果这里不成立这一段就不会执行了 所以需要来一个union all 就是如果不，就筛出tag = 1就行。

通过代码2

with t as 
(
select 
    t.uid,
    level,
    sum(if(start_time is not null and submit_time is null,1,0))  incomplete_cnt,
    round(sum(if(start_time is not null and submit_time is null,1,0))/count(1),3)  incomplete_rate,
    count(exam_id)  num
from 
    user_info t
left join 
    exam_record t1 on t.uid = t1.uid
group by 
    t.uid
)

select 
    uid,
    incomplete_cnt,
    incomplete_rate
from 
    t
where EXISTS 
(select 
     uid 
 from 
     t 
 where 
     level=0 and incomplete_cnt>2
) and level=0

union ALL

select 
    uid,
    incomplete_cnt,
    incomplete_rate
from 
    t
where not EXISTS (
    select 
        uid 
    from 
        t 
    where 
        level=0 and incomplete_cnt>2
) and num>0
order by 
    incomplete_rate

上边那个是拆题来的，就是没有任何优化或者设计，直接题怎么出我怎么来，所以就有了这个，但是基本道理还是一样的。