如何将前5名的薪水和后5名的薪水结合起来显示在Oracle中？

小编典典

如何将前5名的薪水和后5名的薪水结合起来显示在Oracle中？

sql

因此，我知道如何自行显示前5名和后5名。问题是如何将两者同时显示。这就是我所拥有的，但只显示底部的5。

SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL DESC
)
WHERE ROWNUM <6 AND

SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL ASC
)
WHERE ROWNUM <6;

阅读 128

2021-04-15

共1个答案

小编典典

使用UNION或UNION ALL：

SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL DESC
)
WHERE ROWNUM < 6
UNION ALL
SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL ASC
)
WHERE ROWNUM < 6;

您还可以更简洁地将其编写为：

SELECT SAL
FROM (SELECT DISTINCT SAL,
             DENSE_RANK() OVER (ORDER BY sal) as seqnum_asc,
             DENSE_RANK() OVER (ORDER BY sal) as seqnum_desc
      FROM EMP
      WHERE SAL IS NOT NULL
     ) s
WHERE seqnum_asc < 6 OR seqnum_desc < 6;

2021-04-15