sky丶Mamba2

题解

c++(1) c语言(2) Java后端(1) mysql(2) PAT(1) PAT (Basic Level)(76) 其它(5) 大学文档(7) 数据分析(6) 社区发现(1) 算法(1)

/ 注册

全部文章 / 题解（共80篇）

题解 | 18#获取当前薪水第二多的员工的emp_no以及其对应的薪水salary# 来自专栏

select e.emp_no ,salary ,last_name ,first_name from employees e join salaries s on e.emp_no=s.emp_no where salary = ( select max(salary) fr...

2021-08-16

0 359

题解 | 54#查找排除当前最大、最小salary之后的员工的平均工资avg_salary# 来自专栏

select avg(salary) avg_salary from salaries where salary != (select max(salary) from salaries where to_date='9999-01-01') and salary != (select min(s...

2021-08-16

0 401

题解 | 71#牛客每个人最近的登录日期(六)# 来自专栏

select u.name u_n,p.date,sum(number) over(partition by name order by date) ps_num from user u join passing_number p on u.id=p.user_id order by date,u_...

2021-08-16

0 317

题解 | 89#获得积分最多的人(一)# 来自专栏

select name,sum(grade_num) grade_sum from user u join grade_info g on u.id=g.user_id group by name order by grade_sum desc limit 1思路：用一个排序和limit就好了，不...

2021-08-16

0 269

题解 | 25#获取员工其当前的薪水比其manager当前薪水还高的相关信息# 来自专栏

select de.emp_no ,dm.emp_no as manager_no ,s1.salary as emp_salary ,s2.salary as manager_salary from dept_emp de join dept_manager dm on de.dept...

2021-08-16

0 416

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

select emp_no,salary,dense_rank() over (order by salary desc) from salaries order by salary desc,emp_no思路：利用窗口函数的 dense_rank() ，后面对salary和emp_no 进行排序

2021-08-15

0 352

题解 | 59#获取有奖金的员工相关信息。# 来自专栏

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary, case when eb.btype=1 then s.salary*0.1 when eb.btype=2 then s.salary*0.2 else s....

2021-08-15

0 341

题解 | 24#获取所有非manager员工当前的薪水情况# 来自专栏

select de.dept_no,de.emp_no,s.salary from dept_emp de join employees e on de.emp_no=e.emp_no join dept_manager dm on de.dept_no=dm.dept_no join s...

2021-08-15

0 364

题解 | 75#考试分数(四)# 来自专栏

select job,floor((count(*)+1)/2) start,ceil((count(*)+1)/2) end from grade group by job order by job思路：求中位数的上下位注意在原数值基础上加一再除二，然后利用floor和ceil函数进行向下和向...

2021-08-15

0 300

题解 | 22#统计各个部门的工资记录数# 来自专栏

select dm.dept_no,dm.dept_name,count(*) from departments dm join dept_emp de on dm.dept_no=de.dept_no join salaries s on de.emp_no=s.emp_no grou...

2021-08-15

0 340