【难度】：经典题

【场景】：计算结果不同输出结果不同

【分类】：高级条件语句、if(select 语句)、where or(条件不同时满足)、where if/case when(根据计算结果输出不同条件)

分析思路

难点：

1.计算结果不同输出结果不同

收获：

1.if()里面也可以嵌套select子句

2.where or（两个条件不会同时满足，相当于执行or的左边否则执行右边）

3.left join 中使用 on 1 = 1，连接条件 1 = 1 表示无条件的 left join，它返回来自左表表达式的所有行，即使在右表表达式中没有匹配。

4.where 里面使用 if，根据是否存在 ‘未完成试卷数大于2的用户’ ，得到其他条件，进而确定输出结果。

（1）当有任意一个0级用户未完成试卷数大于2

因为输出用户以user_info表为主

  • [使用]：左连接 left join

  • [条件]：level = 0 and count(incomplete_cnt) > 2

计算每个用户的试卷未完成数：

  • [使用]：sum(if(uid in (select uid from exam_record) and submit_time is null,1,0))

未完成率：

  • [使用]：count(if(submit_time is null,1,null)/count(start_time)

（2）若不存在这样的用户

输出用户以exam_record表为主

  • [使用]：右连接 right join

计算每个用户的试卷未完成数：

  • [使用]：count(if(submit_time is null,1,null))

未完成率：

  • [使用]：count(if(submit_time is null,1,null)/count(start_time)

（3）根据指定记录是否存在输出不同情况，并按未完成率升序输出

  • [使用]：sum(incomplete_cnt) > 0

求解代码

方法一：

with子句 + if(select )

with
    main as(
        #当有任意一个0级用户未完成试卷数大于2，其未完成数和未完成率
        select
            a.uid,
            sum(if(uid in (select uid from exam_record) and submit_time is null,1,0)) as incomplete_cnt,
            round(if(count(start_time) = 0,0,count(if(submit_time is null,1,null))/count(start_time)),3) as incomplete_rate
        from user_info a
        left join exam_record using(uid)
        where level = 0
        group by uid 
        order by incomplete_rate
    )
    ,attr as(
        #不存在0级用户未完成试卷数大于2，其未完成数和未完成率
        select
            b.uid,
            count(if(submit_time is null,1,null)) as incomplete_cnt,
            round(if(count(start_time) = 0,0,count(if(submit_time is null,1,null))/count(start_time)),3) as incomplete_rate
        from user_info a
        right join exam_record b using(uid)
        group by uid
        order by incomplete_rate
    )
#根据指定记录是否存在输出不同情况，并按未完成率升序输出
select distinct
    if((select sum(incomplete_cnt) from main where incomplete_cnt > 2) > 0,a.uid,b.uid) as uid,
    if((select sum(incomplete_cnt) from main where incomplete_cnt > 2) > 0,a.incomplete_cnt,b.incomplete_cnt) as incomplete_cnt,
    if((select sum(incomplete_cnt) from main where incomplete_cnt > 2) > 0,a.incomplete_rate,b.incomplete_rate) as incomplete_rate
from main a, attr b
order by incomplete_rate

方法二：

where or（两个条件不会同时满足，相当于执行or的左边否则执行右边）

left join 中使用 on 1 = 1，连接条件 1 = 1 表示无条件的 left join，它返回来自左表表达式的所有行，即使在右表表达式中没有匹配。

select
    a.uid,
    if(attr.incomplete_cnt is null,0,attr.incomplete_cnt) as incomplete_cnt,
    if(round(attr.incomplete_rate,3) is null,0,round(attr.incomplete_rate,3)) as incomplete_rate
from user_info a
left join(
    #不存在0级用户未完成试卷数大于2，其未完成数和未完成率
    select 
        uid,
        sum(if(uid in (select uid from exam_record) and submit_time is null,1,0)) as incomplete_cnt,
        round(if(count(start_time) = 0,0,count(if(submit_time is null,1,null))/count(start_time)),3) as incomplete_rate
    from exam_record
    group by uid 
) attr on a.uid = attr.uid
left join(
    #当有任意一个0级用户未完成试卷数大于2，其未完成数和未完成率
    select
        b.uid
    from exam_record b
    left join user_info a on a.uid=b.uid
    where level=0
    group by uid
    having sum(case when submit_time is null then 1 else 0 end) >= 2
) main on 1 = 1
where (main.uid is not null) or (main.uid is null and attr.uid is not null) #两个条件不会同时满足，所以可以使用 or
order by incomplete_rate

方法三：

where 里面使用 if，根据是否存在 ‘未完成试卷数大于2的用户’ ，得到其他条件，进而确定输出结果。

如果 ‘ 未完成试卷数大于2’ 的用户 不存在，条件是 ’用户的作答记录不为空‘ ，否则得到 ’用户等级为 0‘。

select
    a.uid,
    count(b.uid is not null and score is null or null) as incomplete_cnt,
    round(count(b.uid is not null and score is null or null)/if(count(b.uid)=0,1,count(b.uid)),3) as incomplete_rate
from user_info a
left join exam_record b on a.uid = b.uid
where if((
    #当有任意一个0级用户未完成试卷数大于2,输出每个0级用户的试卷未完成数和未完成率;若不存在这样的用户，则输出所有有作答记录的用户的这两个指标
    select
        count(*)
    from exam_record c
    left join user_info d using(uid)
    where score is null and level=0
    group by uid
    having count(*) > 2
    limit 1) is null,
    b.uid is not null,
    a.level=0
)
group by uid
order by incomplete_rate