牛客567868013号

SQL题解

题解(18)

/ 注册

全部文章 / SQL题解（共12篇）

题解 | #牛客每个人最近的登录日期(六)#

select user.name,b.date,b.ps_num from (select pn.user_id,pn.date,sum(pn2.number) as ps_num from passing_number pn inner join passing_number pn2 on pn...

Sqlite

2021-09-29

0 274

题解 | #牛客每个人最近的登录日期(四)#

select login.date, case when b.user_id is NULL then 0 else b.new_id end as new #将没有新用户（null）的日期对应的new值赋0 from login left join (select *,count(user_id)...

Sqlite

2021-09-29

0 299

题解 | #牛客每个人最近的登录日期(三)#

select round(count(login.user_id)*1.0/count(b.user_id),3) as p from (select *, date(a.date,'+1 day') as next from (select * ,rank() over (PARTITION by...

Sqlite

2021-09-29

0 340

题解 | #牛客每个人最近的登录日期(二)#

#窗口函数的使用 select user.name,client.name,a.date from (select *, rank() OVER(PARTITION by user_id ORDER by date DESC) as number from login) as a left joi...

Sqlite

2021-09-29

0 320

题解 | #异常的邮件概率#

#思路：变相用sum来统计个数 select a.date,round(sum(fail_num)*1.0/count(*),3) as p from (select * , CASE WHEN type=='no_completed' THEN 1 ELSE 0 END as fail_num ...

Sqlite

2021-09-29

0 357

题解 | #对于employees表中，给出奇数行的first_name#

#坑在于输出的名字顺序要按照employee表存储的顺序来输出，需要在where再加一层子查询 select e.first_name FROM employees e where e.emp_no in (select a.emp_no FROM (SELECT * , row_number() ...

Sqlite

2021-09-29

0 373

题解 | #统计salary的累计和running_total#

#思路:使用自连结和row_number（），找到所有row_number小于等于当前行row_number的 select a.emp_no,a.salary,sum(b.salary) as running_total from (SELECT *,row_number() OVER() as...

Sqlite

2021-09-29

0 427

题解 | #获取有奖金的员工相关信息。#

#学习在select里使用case when else end结构。 select eb.emp_no,e.first_name,e.last_name,eb.btype,a.salary, CASE WHEN eb.btype==1 THEN a.salary*0.1 WHEN ...

Sqlite

2021-09-29

0 346

题解 | #获取所有非manager员工当前的薪水情况#

#使用inner join 排除了没有工资的员工和没有分配部门的员工。 select de.dept_no,s.emp_no,salaries.salary from (select e.emp_no from employees e where e.emp_no not in (select em...

Sqlite

2021-09-29

0 389

题解 | #对所有员工的薪水按照salary降序进行1-N的排名#

#学习使用窗口函数rank，dense_rank，row_number select s.emp_no,s.salary, dense_rank() OVER (ORDER by salary DESC) as t_rank from (select * from salaries order by...

Sqlite

2021-09-29

0 279