iUAE
iUAE
全部文章
分类
题解(36)
归档
标签
去牛客网
登录
/
注册
iUAE的博客
全部文章
(共21篇)
题解 | #考试分数(三)#
通过窗口函数排序算出工资排名,再作个表子连接 筛选排名<=2 的即可 select y.id,o,y.score from grade as y join ( select t.id,c.name as o, dense_rank() over(partition by c.name orde...
Mysql
2022-06-16
0
172
题解 | #考试分数(二)#
算出平均工资表连接即可 select id,t.job,t.score from grade as t join (select job ,avg(score) as 平均工资 from grade group by job) as c on c.job=t.job where t.score>...
Mysql
2022-06-16
0
139
题解 | #牛客每个人最近的登录日期(六)#
注意只用两个表,题目指明又登陆没有刷题的情况不记 select g.name,t.date,sum(number) over(partition by g.name order by t.date asc) from passing_number as t join user as g on t.u...
Mysql
2022-06-15
0
130
题解 | #牛客每个人最近的登录日期(三)#
解决这种注册后留存,新增用户数问题,关键在于构建一个用户注册表 select r.date,sum(r.date=注册日期) from login as r join ( select user_id,min(date)as 注册日期 from login group by user_id) as ...
Mysql
2022-06-09
0
126
题解 | #牛客每个人最近的登录日期(三)#
次日留存率关键就是对时间差=1条件计数 select round(sum(timestampdiff(day,注册日期,date)=1)/count(distinct t.user_id),3) from login as t right join (select user_id as g,min...
Mysql
2022-06-08
0
168
题解 | #牛客每个人最近的登录日期(二)#
三表连接对日期排名,取最大 select u_n,c_n, 最近日期 from ( select c.name as u_n,b.name as c_n,max(t.date) as 最近日期 ,dense_rank() over(partition by c.name order by max(d...
Mysql
2022-06-08
0
158
题解 | #给出employees表中排名为奇数行的first_name#
连接子查询表即可,最后and和wehere都可以,但是所代表的意义不同,结果一样 select t.first_name from employees as t join (select first_name as r,dense_rank() over(order by first_name) a...
Mysql
2022-06-07
0
153
题解 | #统计salary的累计和running_total#
窗口函数使用,确保严谨外面order by不能省略,窗口函数排序只对计算新字段有作用 select emp_no,sum(salary), sum(sum(salary)) over(order by emp_no) from salaries where to_date='9999-01-01'...
Mysql
2022-06-06
0
138
题解 | #获取有奖金的员工相关信息。#
方法一:三个表连续连接,考察了if嵌套或者是CASE When select b.emp_no,b.first_name,b.last_name,c.btype,t.salary,round(if(c.btype=1,t.salary0.1,if(c.btype=2,t.salary0.2,t.sa...
Mysql
2022-06-06
0
207
题解 | #平均工资#
-- in后面只能有一个字段。 -- <>后面只能有一个字段.不能同时与一个查询出两个字段的临时表比较,salary是一个字段,不能和两个字段比较 -- 注意排除在职(to_date = '9999-01-01' )员工 select avg(salary) as avg_salary ...
Mysql
2022-06-03
0
143
首页
上一页
1
2
3
下一页
末页