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

问题描述

所以我知道如何让前5名和后5名自己显示.问题是如何将两者同时显示.这就是我所拥有的，但仅显示底部5.

So I know how to get the top 5 and bottom 5 to display by themselves. Problem is how do I combine both to display at the same time. This is what I have but it only shows the bottom 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;

推荐答案

使用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;

您还可以更简洁地编写为:

You can also write this more succinctly as:

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;

这篇关于如何将前5名的薪水和后5名的薪水结合起来显示在Oracle中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！