牛客煲
牛客煲
全部文章
题解
归档
标签
去牛客网
登录
/
注册
牛客煲的博客
全部文章
/ 题解
(共9篇)
题解 | #对所有员工的薪水按照salary降序进行1-N的排名#
答案: SELECT emp_no,salary, CASE WHEN @salary=s.salary THEN @rank WHEN @salary:=s.salary THEN @rank:=@rank+1 END as t_rank from salaries s,(SELECT @...
2021-09-14
0
193
题解 | #查找在职员工自入职以来的薪水涨幅情况#
答案: select e.emp_no, (s2.salary-s1.salary) growth from employees e join salaries s1 on e.emp_no=s1.emp_no and e.hire_date=s1.from_date //入职 join salar...
2021-09-13
0
181
题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#
答案: SELECT e.emp_no,max(s.salary),e.last_name,e.first_name from employees e INNER JOIN salaries s ON e.emp_no=s.emp_no WHERE salary<(SELECT MAX(sal...
2021-09-13
0
214
题解 | #统计出当前各个title类型对应的员工当前薪水对应的平均工资#
答案: SELECT title,AVG(s.salary) as a from titles t INNER JOIN salaries s WHERE t.emp_no=s.emp_no GROUP BY title ORDER BY a asc 按title分组,计算平均工资并且按平均工资排序
2021-09-13
0
260
题解 | #获取每个部门中当前员工薪水最高的相关信息#
答案: SELECT re.dept_no,re.emp_no,re.salary FROM (SELECT dept_no,d.emp_no,salary FROM dept_emp d INNER JOIN salaries s on d.emp_no=s.emp_no ORDER BY s...
2021-09-13
1
259
题解 | #获取所有员工当前的manager#
答案: SELECT d.emp_no,dd.emp_no as manager FROM dept_emp d LEFT JOIN dept_manager dd ON d.dept_no=dd.dept_no WHERE d.emp_no NOT IN (SELECT de.emp_no F...
2021-09-13
1
383
题解 | #获取所有非manager的员工emp_no#
SELECT e.emp_no FROM employees e WHERE e.emp_no not in (SELECT e.emp_no FROM employees e INNER JOIN dept_manager d on e.emp_no = d.emp_no)筛选在部门的员工:SE...
2021-09-08
1
260
题解 | #查找当前薪水详情以及部门编号dept_no#
select sa.emp_no,sa.salary,sa.from_date,sa.to_date,de.dept_no from dept_manager de left join salaries sa on sa.emp_no=de.emp_no order by sa.emp_no as...
2021-09-08
1
227
题解 | #查找入职员工时间排名倒数第三的员工所有信息#
select * from employees order by hire_date desc limit 3从这筛选出前三条知道需要第三条,所以limit(indx,count),index从哪里开始,count取多少条答案:select * from employees order by h...
2021-09-08
1
338