SQL ORDER BY DECODE将数字排序为字符串?
我有创造的飞行记录的表如下SQL ORDER BY DECODE将数字排序为字符串?
CREATE TABLE FLIGHT_DETAILS
(
FLIGHT_ID NUMBER(10) PRIMARY KEY,
FLIGHT_NO VARCHAR2(10),
DEPARTURE_DTE DATE,
TOTAL_PASSENGERS NUMBER(3)
);
然后,我有我的应用程序调用来检索根据所选列排序的记录(按降序排列)的功能。
CREATE OR REPLACE FUNCTION func_get_flight_details (
p_order_col IN CHAR)
RETURN sys_refcursor
AS
v_ref_cursor sys_refcursor;
v_sql_str VARCHAR2(2048);
BEGIN
OPEN v_ref_cursor FOR
SELECT FLIGHT_NO, DEPARTURE_DTE, TOTAL_PASSENGERS
FROM FLIGHT_DETAILS
ORDER BY DECODE(p_order_col,
'FLIGHT_NO', FLIGHT_NO,
'DEPARTURE_DTE', TO_DATE(DEPARTURE_DTE, 'YYYY/MM/DD'),
'TOTAL_PASSENGERS', TOTAL_PASSENGERS) DESC;
RETURN v_ref_cursor;
END;
双方FLIGHT_NO
和DEPARTURE_DTE
排序工作正常。我的问题是当我试图通过TOTAL_PASSENGERS
进行排序,这让我这个
FLIGHT_NO DEPARTURE_DTE TOTAL_PASSENGERS
-------------------------------------------------
OR3237 01/03/16 9
RM7202 15/01/16 50
CQ8429 05/10/16 250
DA5720 21/07/16 100
出于某种原因,DECODE被排序NUMBER
列的字符串。为了测试,我想这
SELECT FLIGHT_NO, DEPARTURE_DTE, TOTAL_PASSENGERS
FROM FLIGHT_DETAILS
ORDER BY TOTAL_PASSENGERS DESC;
这给了我
FLIGHT_NO DEPARTURE_DTE TOTAL_PASSENGERS
-------------------------------------------------
CQ8429 05/10/16 250
DA5720 21/07/16 100
RM7202 15/01/16 50
OR3237 01/03/16 9
证明,这个问题是不是与列本身。
然后我尝试了一些解决方案,我对SO
ORDER BY DECODE(p_order_col,
'FLIGHT_NO', FLIGHT_NO,
'DEPARTURE_DTE', TO_DATE(DEPARTURE_DTE, 'YYYY/MM/DD'),
'TOTAL_PASSENGERS', TO_NUMBER(TOTAL_PASSENGERS)) DESC;
ORDER BY DECODE(p_order_col,
'FLIGHT_NO', FLIGHT_NO,
'DEPARTURE_DTE', TO_DATE(DEPARTURE_DTE, 'YYYY/MM/DD'),
'TOTAL_PASSENGERS', LPAD(TOTAL_PASSENGERS, 10)) DESC;
ORDER BY DECODE(p_order_col,
'FLIGHT_NO', FLIGHT_NO,
'DEPARTURE_DTE', TO_DATE(DEPARTURE_DTE, 'YYYY/MM/DD'),
'TOTAL_PASSENGERS', TOTAL_PASSENGERS*1) DESC;
其中没有工作(它仍然归类为一个字符串)发现。
那么为什么DECODE
拒绝将数字列作为数字进行排序呢?我如何才能正确排序?
你的问题是decode()
是一个表达式,只返回一个类型。所以,类型必须转换。您可以使用case
。我的首选方法是多条语句:
ORDER BY (CASE WHEN p_order_col = 'FLIGHT_NO' THEN FLIGHT_NO END),
(CASE WHEN p_order_col = 'DEPARTURE_DTE' THEN TO_DATE(DEPARTURE_DTE, 'YYYY/MM/DD') END)
(CASE WHEN p_order_col = 'TOTAL_PASSENGERS' THEN TOTAL_PASSENGERS END) DESC;
每个表达式都按一个键排序。如果排序键不匹配,则表达式的结果为NULL
- 所有行的值都相同,因此不会影响排序。
Gordon发布的示例答案中几乎没有性能影响。你并不是真的在做三种不同的分类;它仍然是一个单独的排序操作。快速测试将显示此:
select ename, sal, mgr
from emp
order by (case when 'SAL' = 'SAL' then sal end)
, (case when 'SAL' = 'NAME' then ename end)
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21 (100)| |
| 1 | SORT ORDER BY | | 14 | 196 | 21 (10)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| EMP | 14 | 196 | 20 (5)| 00:00:01 |
-----------------------------------------------------------------------------------
然而,当你试图写一个“通用” SQL语句来处理各种不同的情况下,我要提醒的方法。虽然这种“聪明”的SQL语句可以在功能上工作,但它可能会成为性能的灾难。拥有单独的SQL语句要好得多。在这里发布的这个例子中,有三条不同的SQL语句相当容易,每条语句都有一个特定的ORDER BY
子句,然后在输入参数上使用一个简单的IF THEN ELSE
来确定要运行的SQL语句
您的意思是性能问题是使用'case'还是'decode'?我根据输入使用单独的语句。然而,我的实际表格有9列可以排序,一些相当复杂的聚合/连接集中在内部。我极大地简化了这个问题,以突出“解码”问题。将它们拆分为9个单独的查询听起来像维护地狱:( – sml485
@ sml485。我的评论有些泛泛,但我已经看到了非常复杂的SQL语句,开发人员试图很聪明,其构造如下所示:SELECT * FROM emp WHERE ename =:1 OR:1 IS NULL)和(job =:2 OR:2 IS NULL)。问题是优化器没有办法确定谓词的选择性,因此性能是次优的所以我只是警告使用“超级通用”的SQL语句,是的,你将有权衡维护成本;但是我也建议更简单的SQL语句更容易调试和维护。 – BobC
看起来很棒!那会不会有性能问题?因为它看起来像你正在做3个单独的排序而不是1个(即使其中2个总是'null'。 – sml485
@ sml485。请参阅我在附加答案中的回复。 – BobC
@ sml485 ...不,没有额外的性能按三个键排序几乎与按一个键排序相同 –