个人提交答案汇总,part4
解答比较繁琐,有建议或好的想法欢迎评论区指出探讨=w=
1-20 答案链接在这里
21-40 答案链接在这里
41-60 答案链接在这里
61、对于employees表中,输出first_name排名(按first_name升序排序)为奇数的first_name
select a.first_name from (
select first_name , emp_no, row_number() over(order by first_name) as cnt
from employees ) a
where a.cnt % 2 <> 0
order by a.emp_no
这道题就离谱,结果说不需要排序然后同样的答案,但是顺序不对就是ac不了
62、写一个sql查询,积分表里面出现三次以及三次以上的积分
select number
from grade
group by number
having count(*) >= 3
63、输出通过的题目的排名,通过题目个数相同的,排名相同,此时按照id升序排列
select id
,number
,(
select count(distinct number)
from passing_number b
where a.number < b.number
) + 1 as t_rank
from passing_number a
order by number desc , id
觉得应该有函数实现,所以看了评论区,有个小伙伴是这样写的,由此可见,学好窗口函数多么重要
SELECT
id,number,
dense_rank ( ) over ( ORDER BY number DESC ) `rank`
FROM
passing_number
ORDER BY
`rank`,
id
- rank() | dense_rank | row_number() 函数区分
- rank() : 如果存在并列情况,会占用下一个名次的位置:1、1、3、4、4、6
- dense_rank() : 如果存在并列情况,不占用下一个名次的位置:1、1、2、3、3、4
- row_number() : 忽视并列情况:1、2、3、4、5、6
64、请你找到每个人的任务情况,并且输出出来,没有任务的也要输出,而且输出结果按照person的id升序排序
select p.id
,p.name
,t.content
from person p left join task t on p.id = t.person_id
order by p.id
65、统计正常用户发送给正常用户邮件失败的概率:
select total.date as date
,round(sb.sb_p / total.total_p, 3) as p
from
(select date, count(*) as total_p from email
where send_id in (select id from user where is_blacklist = '0') and
receive_id in (select id from user where is_blacklist = '0')
group by date
) total join
(select date, count(*) as sb_p from email where type = "no_completed"
and send_id in (select id from user where is_blacklist = '0') and
receive_id in (select id from user where is_blacklist = '0')
group by date
) sb on total.date = sb.date
这道题我写的真的是繁琐又无脑,没有一点艺术性。。。
下面是牛客里其他小伙伴写的答案,感觉都很好,大家都是用case when解决的
select date,round(sum(case e.type when 'no_completed' then 1 else 0 end)*1.0/count(e.type),3) as p
from email e
where send_id in (select id from user where is_blacklist = 0)
and receive_id in (<