sql - 如何按升序和降序对同一列进行排序

Question

使用 SQL 查询，我们如何获得 2 列的输出，第一列是按 asc 顺序排序的列，第二列是按 desc 排序的列，两者都是相同的列。

例如:

emp table:
empid
1 
5
9
4

查询输出应该是

empid_1   empid_2
1          9
4          5
5          4
9          1

到目前为止 OP 尝试了什么

WITH emp1 
     AS (SELECT ROWNUM a, 
                empno 
         FROM   (SELECT empno 
                 FROM   emp 
                 ORDER  BY 1 ASC)), 
     emp2 
     AS (SELECT ROWNUM b, 
                empno 
         FROM   (SELECT empno 
                 FROM   emp 
                 ORDER  BY 1 DESC)) 
SELECT emp1.empno, 
       emp2.empno 
FROM   emp1, 
       emp2 
WHERE  emp1.a = emp2.b; 

Answer 1

您可以使用 row_number() 和自连接来做到这一点:

select e1.empid as empid_1, e2.empid as empid_2
from (select e.*, row_number() over (order by emp_id) as seqnum
      from emp e
     ) e1 join
     (select e.*, row_number() over (order by emp_id desc) as seqnum
      from emp e
     ) e2
     on e1.seqnum = e2.seqnum;

编辑:

您也可以使用 rownum 执行此操作，但它需要额外的 select:

select e1.empid as empid_1, e2.empid as empid_2
from (select e.*, rownum as seqnum
      from (select e.* from emp e order by empid asc) e
     ) e1 join
     (select e.*, rownum as seqnum
      from (select e.* from emp e order by empid desc) e
     ) e2
     on e1.seqnum = e2.seqnum;