查询每个部门工资前两名的通用sql解析

最新推荐文章于 2025-03-06 14:31:18 发布

最新推荐文章于 2025-03-06 14:31:18 发布 · 1k 阅读

文章标签：

#SQL #Oracle

sql 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用SQL查询每个部门薪资排名前两名员工的方法。通过对比Oracle、MSSQL及通用SQL实现方式，详细解析了如何利用相关子查询进行分组计数，以筛选出所需数据。

emp表结构：

[quote]
名称是否为空? 类型
----------------------------------------- -------- ----------------------------

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
[/quote]
[quote]
DEPTNO ENAME SAL
---------- ---------- ----------
20 SMITH 860
30 ALLEN 1750
30 WARD 1400
20 JONES 3035
30 MARTIN 1400
30 BLAKE 3000
10 CLARK 2480
20 SCOTT 3060
10 KING 5030
30 TURNER 1650
20 ADAMS 1160
30 JAMES 1100
20 FORD 3060
10 MILLER 1330
[/quote]
Oracle查询：（利用分区功能）


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

MS SQL：（top）


SELECT TOP 2(SELECT COUNT(*) FROM EMP 
      WHERE SAL>=A.SAL) AS mc,A.sal FROM EMP AS A
      GROUP BY DEPTNO ORDER BY deptno SAL desc;

通用sql：


select deptno, ename, sal 
from emp e1
where 
   (select count(1)
   from emp e2
   where e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal)
   <2
order by deptno, sal desc;

现在开始分析这个通用的：
1、


select deptno, ename, sal from emp e1 
   order by deptno, sal desc;

先不考虑查询条件，表 e1 按照deptno 正序、sal倒序排列
[quote]
E1
DEPTNO ENAME SAL
10 KING 5030
10 CLARK 2480
10 MILLER 1330
20 SCOTT 3060
20 FORD 3060
20 JONES 3035
20 ADAMS 1160
20 SMITH 860
30 BLAKE 3000
30 ALLEN 1750
30 TURNER 1650
30 MARTIN 1400
30 WARD 1400
30 JAMES 1100
[/quote]
2、要查询 e1表中的数据，且要参考e2中数据。e1是主表，e2是附表。
下面来看


where e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal

这个条件，其中e2.ename!=e1.ename在这个表中基本可以忽略，因为emp表中没有
deptno、ename、sal都相同的记录，这里它只是起到个确保准确性的作用，不是这个问
题的关键条件。


e2.deptno=e1.deptno and e2.sal>e1.sal

抽取emp中deptno=10的记录，根据这个条件，你会发现：
[quote]
E2 E1
DEPTNO=10 ENAME SAL DEPTNO=10 ENAME SAL
10 KING 5030
10 KING 5030 10 CLARK 2480
10 KING 5030 10 MILLER 1330
10 CLARK 2480 10 MILLER 1330
10 MILLER 1330
[/quote]


where (select count(1) from emp e2 where   
e2.deptno=e1.deptno and e2.ename!=e1.ename and e2.sal>e1.sal) 
   <2

count(1)求的是满足条件的记录数。
当 count(1)=0 时，
[quote]
E2 E1
DEPTNO=10 ENAME SAL DEPTNO=10 ENAME SAL
10 KING 5030
[/quote]
表 e2 中没有记录，表 e1中是该部门工资最高的。

当 count(1)=1 时，
[quote]
E1 E2
DEPTNO=10 ENAME SAL DEPTNO=10 ENAME SAL
[color=white] 10 KING 5030[/color]
10 KING 5030 10 CLARK 2480
[/quote]
表 e2 中是该部门工资最高的，表 e1中是该部门工资次高的。

当 count(1)=2 时，
[quote]
E2 E1
DEPTNO=10 ENAME SAL DEPTNO=10 ENAME SAL
[color=white] 10 KING 5030
10 KING 5030 10 CLARK 2480[/color]
10 KING 5030 10 MILLER 1330
[/quote]
表 e2 中是该部门工资最高的，表 e1中是该部门工资第三高的。

至于为什么会分组求 count(1)，是因为相关子查询实现了分组。