1、SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
select e.emp_no, s.salary, e.last_name, e.first_name
from salaries as s
join employees as e on e.emp_no = s.emp_no
where s.salary =
(select s1.salary from salaries as s1 join salaries as s2
on s1.salary <= s2.salary
group by s1.salary
having count(distinct s2.salary) = 2)
来自:牛客题目
- having count 是用来计数,having count(distinct s2.salary) = 2 是用来得到个数为2的salaries
- 内链接是用来做笛卡尔积,薪水表笛卡尔积,group by 后就可以拿到大于当前薪水的其他薪水合集
这是一个通用的解法,可以扩展到第三名,第四名等场景。
SQL查询每门课程最高分及人数
select course,sudResult,COUNT(course)stuCount from (
select * from stud a where sudResult=(select max(sudResult) from stud where course=a.course)
)b
group by course,sudResult
这与上面一样的问题,但另一种解法,似乎很拗口,但这里可以提供一个思路来解答。来自SQL查询每门课程最高分及人数
还有一种方式是将查询结果作为临时表,与原表进行join,来查询。
SELECT e1.salary
FROM employees e1,
(SELECT MAX(salary) AS max_salary,e.`department_id` did
FROM employees e
GROUP BY e.`department_id`
ORDER BY max_salary
LIMIT 0,1
)AS temp
WHERE e1.department_id = temp.did