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

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

http://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b

明确题意:

当有0级用户未完成试卷数大于2时输出每个0级用户的试卷未完成数和未完成率(保留3位小数); 若不存在这样的用户,则输出所有有作答记录的用户的这两个指标。

结果按未完成率升序排序。


问题分解:

  • 统计每个用户的等级、未完成数、未完成率和总作答数,生成临时表 t_tag_count:
    • 右连接试卷作答表和用户信息表:exam_record RIGHT JOIN user_info USING(uid)
    • 按用户分组:GROUP BY uid
    • 统计未完成数:COUNT(start_time) - COUNT(submit_time) as incomplete_cnt
    • 统计未完成率:IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0) as incomplete_rate
      • 保留3位小数:ROUND(x, 3)
    • 统计总作答数:COUNT(start_time) as total_cnt
  • 当存在0级用户未完成试卷数大于2时:
    • 筛选存在性条件:WHERE EXISTS (SELECT uid FROM t_tag_count WHERE level = 0 AND incomplete_cnt > 2)
    • 输出每个0级用户的试卷未完成数和未完成率:
      • 筛选0级用户:level = 0
      • SELECT uid, incomplete_cnt, incomplete_rate
  • 当不存在0级用户未完成试卷数大于2时:
    • 筛选存在性条件:WHERE NOT EXISTS (SELECT uid FROM t_tag_count WHERE level = 0 AND incomplete_cnt > 2)
    • 输出所有有作答记录的用户的这两个指标:
      • 筛选有作答记录的用户:total_cnt > 0
      • SELECT uid, incomplete_cnt, incomplete_rate
  • 合并上述结果,条件互斥,so只可能有一个结果集:UNION ALL

细节问题:

  • 表头重命名:as
  • 按未完成率升序排序:ORDER BY incomplete_rate

完整代码:

WITH t_tag_count as (
    SELECT uid, `level`,
        COUNT(start_time) - COUNT(submit_time) as incomplete_cnt, -- 未完成数
        ROUND(
            IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0),
            3) as incomplete_rate, -- 此人未完成率
        COUNT(start_time) as total_cnt -- 总作答数
    FROM exam_record
    RIGHT JOIN user_info USING(uid)
    GROUP BY uid
)

SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND `level` = 0
UNION ALL
SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE NOT EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND total_cnt > 0
ORDER BY incomplete_rate;
SQL进阶 文章被收录于专栏

SQL进阶step by step

全部评论
level 不能加引号 用例会无法识别
点赞 回复 分享
发布于 2022-04-16 15:33
想问一下为什么where exists里已经筛选了level = 0, 还要在后面加上level = 0 才可以呢,这是语法规定的吗还是什么,因为后面不再加level = 0 确实还会出现level != 0 的用户
3 回复 分享
发布于 2022-05-04 11:34
还有一个问题是比如1004没答题,他在他在答题记录表里就没有start_time和submit_time,那么right join了之后,他这两列的值就是null,那么count(start_time)-count(submit_time) as incomplete_cnt, count这两列为null的数值之后结果到底是null还是0?
1 回复 分享
发布于 2022-08-06 21:11
level没有放在group by里为啥没有报错??
1 回复 分享
发布于 2022-07-18 15:47
这个有问题的,如果没有0级用户未完成试卷数大于2,也会输出0级用户的试卷未完成数和未完成率
点赞 回复 分享
发布于 2024-09-30 11:54 北京
求问sum(if(submit_time is null,1,0)) as incomplete_cnt, round(sum(if(submit_time is null,1,0))/count(start_time),3) as incomplete_rate,为什么只输出一条值啊,感觉没错啊。将这两句改成博主就对了,不知道为啥
点赞 回复 分享
发布于 2023-04-27 13:48 广东
请教大佬,为什么在count(start_time) total_cnt的时候写成count(score)对结果会有影响呢?反正也是输出有0级用户的呀。
点赞 回复 分享
发布于 2022-12-05 23:23 湖南
请教大佬,下面这个语句问题在哪?提交的时候多一个用户1006, 两个指标的结果都是0 实在想不出问题在哪里。。。 非常感谢 with data_all_level as ( select level, uid , sum(if(submit_time is null and start_time is not null, 1, 0)) incomplete_cnt , round(sum(if(submit_time is null and start_time is not null, 1, 0)) / count(if(start_time is null, 0, 1)), 3) as incomplete_rate from exam_record right join user_info using (uid) group by level, uid ), uid_level_0 as ( select sum(ifnull(uid,0)) level_0_cnt from data_all_level where level = 0 and incomplete_cnt > 2 group by incomplete_cnt ) select uid, incomplete_cnt, ifnull(incomplete_rate, 0) from data_all_level where (if((select level_0_cnt from uid_level_0) > 0 ,(uid in (select uid from data_all_level where level = 0)) ,(uid in (select uid from data_all_level)))) order by incomplete_rate
点赞 回复 分享
发布于 2022-08-04 06:22
老哥,问一下未完成率为什么要用ifnull啊?直接除为什么不行?看了结果未完成率是null,这个null不知道从哪里来的? 我写的是: 1、sum(if(score is null , 1,0)) incomplete_cnt 2、count(start_time) cnt 按照题目说分母不可能为0才对阿
点赞 回复 分享
发布于 2021-12-24 22:14

相关推荐

Rena1ssance_:对的,要是面评没太烂,勤更新简历等捞就行了,腾讯可以无限复活
点赞 评论 收藏
分享
04-25 18:13
五邑大学 Java
后来123321:大二两段实习太厉害了,我现在大二连面试都没有
点赞 评论 收藏
分享
评论
94
10
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务