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

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

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

WITH t1 as(
SELECT ui.uid,
SUM(IF(submit_time IS NULL,1,0)) AS incomplete_cnt,
COUNT(start_time) AS total_cnt
FROM user_info AS ui
JOIN exam_record AS er
ON ui.uid = er.uid
GROUP BY ui.uid
),
t2 as(
SELECT ui.uid,level,
IF(incomplete_cnt IS NULL,0,incomplete_cnt) AS incomplete_cnt,
IF(total_cnt IS NULL,0,total_cnt) AS total_cnt
FROM user_info AS ui
LEFT OUTER JOIN t1 ON ui.uid = t1.uid
),
t3 as(
SELECT uid,level,incomplete_cnt,total_cnt,
IF(incomplete_cnt = 0,0,ROUND(incomplete_cnt / total_cnt,3)) AS incomplete_rate
FROM t2
)
SELECT uid,incomplete_cnt,incomplete_rate
FROM t3
WHERE EXISTS(SELECT uid FROM t3 WHERE incomplete_cnt > 2 AND level = 0) AND level = 0
UNION ALL
SELECT uid,incomplete_cnt,incomplete_rate
FROM t3
WHERE NOT EXISTS(SELECT uid FROM t3 WHERE incomplete_cnt > 2 AND level = 0) AND total_cnt > 0
ORDER BY incomplete_rate ASC;

全部评论

相关推荐

神哥不得了:神哥来啦~自我评价和校园经历的话可以直接删了,从大厂暑期的话应该没有什么太多问题,应该是能拿到很多大厂面试机会的,就是在面试的时候表示的好一点就行,可以在面试前先把高频top 50的八股多巩固几遍,千万不要看那些假高频八股,这两个项目的话问题不是很大,应该能够帮你找到大厂实习的,算法的话一定要刷起来,因为大厂有些还是比较看重算法的
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客企业服务