如何以升序和降序对同一列进行排序 [英] How to sort a same column both in asc order and desc order

问题描述

使用SQL查询如何获取2列的输出，第一列是按asc顺序排序的列，第二列是按desc顺序排序的列，并且都是相同的列.

With an SQL Query how do we get the output of 2 columns, the first one being a column sorted in asc order and the second one with the order desc and both are same columns.

例如:

emp table:
empid
1 
5
9
4

查询输出应为

empid_1   empid_2
1          9
4          5
5          4
9          1

OP到目前为止尝试了什么

What OP tried so far

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; 

推荐答案

如果使用公用表表达式/子查询分解子句，则只需访问一次表:

If you use a common table expression/sub-query factoring clause then you only need to access the table once:

with the_data as (
 select empid
      , row_number() over ( order by empid ) as id_asc
      , row_number() over ( order by empid desc ) as id_desc
   from emp
        )
select a.empid as empid_asc
     , d.empid as empid_desc
  from the_data a
  join the_data d
    on a.id_asc = d.id_desc

这篇关于如何以升序和降序对同一列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！