Oracle针对不同条件的订购 [英] Oracle order by for different conditions
问题描述
我有一个查询,我想根据条件做出两种订单.
I have a query and i would like to make two kinds of order based on a condition.
例如,如果一个字段为NULL,则我需要下一个命令,否则,我必须下一个命令.我该怎么办?
For example, if a field is NULL, i need to make an order and if it is not, i have to make another order. How i can do it ?
select *
from table_1 t
order by (if t.field1 is null then
order by t.field2 DESC, field3 ASC
else
order by t.field4 ASC, field5 DESC)
这是一个示例代码:我想根据FIELD1的值进行不同的排序(ASC/DESC和不同的列)
This is a sample code: i want to make different order (ASC/DESC and different columns), based on the value of FIELD1
示例
有条件的
ID FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
1 1 2 3 4 5
2 NULL 6 7 8 9
数据
ID PARENT_ID DATA1 DATA2 DATA3
1 1 X Y J
2 1 Z W U
3 2 XY YX O
4 2 ZW WZ I
select d.*
from data d, conditional c
where d.parent_id = c.id
and d.parent_id = 1
order by
case
when c.field1 is null then
data1 asc, data2 desc
else
data3 asc, data1 desc
end
在此示例中,我选择数据行ONE和第二行(父ID = 1的行). 现在,我已经做出了这个决定,我想基于CONDICTIONAL.FIELD1列的值来处理DATA列.我希望现在更干净.
In this example i select the DATA rows ONE and TWO (the rows that have parent id = 1). Now that i have make this decision, i want to orther the DATA columns based on the value of CONDICTIONAL.FIELD1 column. I hope now is more clean.
确保此查询确实有效,但这是我需要的".
Sure this query doesent work but this is "what i will need."
推荐答案
您可以在ORDER BY
子句中使用CASE
构造:
You can have CASE
construct in the ORDER BY
clause :
SQL> SELECT * FROM EMP T
2 ORDER BY
3 CASE
4 WHEN COMM IS NULL
5 THEN SAL
6 ELSE EMPNO
7 END
8 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
14 rows selected.
注意:照顾ORDER BY
子句中列的DATA TYPE
.
这篇关于Oracle针对不同条件的订购的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!