sql-面试题

SQL查询每个部门工资前三名的员工信息

1、单个表 

+----+-------+--------+--------------+
| Id | eName  | Sal   | deptno       |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

通用sql

select deptno, ename, sal      
from emp e1     
where      
   (  
    select count(1)     
    from emp e2     
    where e2.deptno=e1.deptno and e2.sal>=e1.sal  
   ) <=3 /*这里的数值表示你想取前几名*/  
order by deptno, sal desc;

oracle查询

select * from         
 (select deptno,ename,sal,row_number() over (partition by deptno        
    order by sal desc) rn         
 from emp)         
    where rn<3;

2、两表关联

表Employee：

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

表Department：

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

 

Select a.Name as Department, b.Name as Employee, b.Salary 
from Department a, Employee b 
where b.DepartmentId = a.Id and (
  Select count(distinct Salary) From Employee where DepartmentId=a.Id and Salary > b.Salary
)<3
order by Department

oracle

select * from (select d.name,e.name,e.Salary,row_number() over (partition by e.DepartmentId        
order by Salary desc) rn         
from Employee e inner join Department d on e.DepartmentId = d.DepartmentId)
where rn<3;

转自：https://www.cnblogs.com/hxzblog/p/7307537.html
           https://www.cnblogs.com/wan1976/p/4766553.html