- 第N高的薪水
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
1、需要使用ifnull(a,b):如果值存在结果为a,否则为b的值(考虑到没有第n高的薪水)
2、对薪水分组取前几个,使用limit offset
limit n子句表示查询结果返回前n条数据
offset n表示跳过x条语句
limit y offset x 分句表示查询结果跳过 x 条数据,读取前 y 条数据
代码:
declare m INT;
set m=N-1;
select ifnull((select distinct Salary from Employee as E order by Salary desc limit m,1),null)
- 部门工资最高的员工
#第一步找到对应部门最高薪水的值—因为多个员工可能有相同薪水
#select DepartmentId,max(Salary) from Employee group by DepartmentId
#按照部门相同,薪水相同找到对应员工
select Department.Name as ‘Department’ ,Employee.Name as ‘Employee’,Salary from Employee
join Department on Employee.DepartmentId = Department.Id where (Employee.DepartmentId,Salary) in
(select DepartmentId,max(Salary) from Employee group by DepartmentId)
596.查找学生选一门课人数>=5
思想:
需要对学生进行筛选,(这里给出的数据集可能有重复的记录),
再对课程分组,选取学生选择该课程的人数为5及以上
select class from (select class,count(distinct student) num from courses group by class ) as temp_table where num>=5