如何以升序和降序对同一列进行排序 [英] 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屋!
查看全文