如何使用不区分大小写的ASC或DESC，以及DISTINCT和UNION进行订购 [英] How to order by case insensitive ASC or DESC, with DISTINCT and UNION
问题描述
对于P/L sql 11g，如何通过不区分大小写的ASC或DESC进行订购.这个p/l sql基本问题，但我在Google中找不到很好的答案，请告诉我们如何对不区分大小写的选择结果进行排序
这是我尝试过的
SELECT DISTINCT
asssss,
saas_acc
FROM DUAL
UNION SELECT '--ALL--','ALL' FROM DUAL
ORDER BY upper(asssss) ASC ;
给我ORA-01785: ORDER BY item must be the number of a SELECT-list expression
DISTINCT实际上使用SELECT子句中给出的任何表达式过滤了结果集中的UNIQUE内容.
我们无法使用其他表达式或列名对其进行排序.请在此处查看示例.
SQL> l
1 SELECT DISTINCT (col1),(col2)
2 FROM
3 ( SELECT 'Hello' col1,'World' col2 FROM DUAL
4 UNION ALL
5 SELECT 'HELLO','WORLD' FROM DUAL
6* )
SQL> /
COL1 COL2
----- -----
HELLO WORLD
Hello World
您可以在此处看到DISTINCT是CASE SENSITIVE.(显示2行)
所以，让我在两列上都做一个UPPER().
SQL> l
1 SELECT DISTINCT UPPER (col1),UPPER(col2)
2 FROM
3 ( SELECT 'Hello' col1,'World' col2 FROM DUAL
4 UNION ALL
5 SELECT 'HELLO','WORLD' FROM DUAL
6* )
SQL> /
UPPER UPPER
----- -----
HELLO WORLD
仅显示1行，忽略大小写.
回到实际问题.要在DISTINCT结果集上排序，它必须是DISTINCT子句的表达式/列的一部分.
因此，当您发出DISTINCT COL1,COl2时，顺序by可能是COL1或COL2/.它不能是COL3或什至是UPPER(COL1)，因为UPPER()会使表达式与表达式冲突超过DISTINCT.
最后，您的问题 答案为
如果您希望您的ORDER不区分大小写，DISTINCT也必须采用相同的方式！如下所示
SELECT DISTINCT
UPPER(asssss),
saas_acc
FROM DUAL
ORDER BY upper(asssss) ASC ;
或者如果必须使用UNION，最好这样做，或者与上面的相同.
SELECT * FROM
(
SELECT DISTINCT asssss as asssss,
saas_acc
FROM DUAL
UNION
SELECT '--ALL--','ALL' FROM DUAL
)
ORDER BY upper(asssss) ASC ;
从我自己的经验中，我一直觉得，ORDER BY中指定的表达式/列都是隐含的到最终的SELECT也一样排序仅基于结果中的列号(位置).在这种情况下，DISTINCT COL1,COl2已经存在.输入ORDER BY UPPER(COL1)时，将尝试将其追加到SELECT表达式中，这完全是NOT可能的.因此，语义检查自身，将使该查询取消资格，并出现错误！
How to order by case insensitive ASC or DESC for P/L sql 11g. this p/l sql basic question but i can't find good answer in Google please tell how to sort the select result case insensitive
this what i tried
SELECT DISTINCT
asssss,
saas_acc
FROM DUAL
UNION SELECT '--ALL--','ALL' FROM DUAL
ORDER BY upper(asssss) ASC ;
that gave to me ORA-01785: ORDER BY item must be the number of a SELECT-list expression
DISTINCT actually filtered the UNIQUE content in the result set, with whatever expressions given in the SELECT clause.
We cannot order it using a Different expression or column name. Please see the example here.
SQL> l
1 SELECT DISTINCT (col1),(col2)
2 FROM
3 ( SELECT 'Hello' col1,'World' col2 FROM DUAL
4 UNION ALL
5 SELECT 'HELLO','WORLD' FROM DUAL
6* )
SQL> /
COL1 COL2
----- -----
HELLO WORLD
Hello World
You can see that DISTINCT is CASE SENSITIVE here.(2 rows displayed)
So, let me Do a UPPER() on both columns.
SQL> l
1 SELECT DISTINCT UPPER (col1),UPPER(col2)
2 FROM
3 ( SELECT 'Hello' col1,'World' col2 FROM DUAL
4 UNION ALL
5 SELECT 'HELLO','WORLD' FROM DUAL
6* )
SQL> /
UPPER UPPER
----- -----
HELLO WORLD
Just 1 row is Displayed, ignoring the case.
Coming back to the actual problem. To order something on a DISTINCT Resultset, it has to be a part of DISTINCT clause's expression/column.
So, When you issue DISTINCT COL1,COl2, the order by may be by COL1 or COL2/.. it cannot be COL3 or even UPPER(COL1) because UPPER() makes a different expression conflicting the expression over DISTINCT.
Finally, Answer for your Question would be
if you want your ORDER to be case-insensitive, DISTINCT also has to the same way! As given below
SELECT DISTINCT
UPPER(asssss),
saas_acc
FROM DUAL
ORDER BY upper(asssss) ASC ;
OR if UNION has to be used, better do this, or same as above one.
SELECT * FROM
(
SELECT DISTINCT asssss as asssss,
saas_acc
FROM DUAL
UNION
SELECT '--ALL--','ALL' FROM DUAL
)
ORDER BY upper(asssss) ASC ;
Out of my own Experience, I had always felt, what ever expression/column is specified in the ORDER BY, it is implicitly taken to final SELECT as well. Ordering is just based on the column number(position) in the result actually . In this situation, DISTINCT COL1,COl2 is already there. When you give ORDER BY UPPER(COL1), it will be tried to append into the SELECT expression, which is NOT possible at all. So, Semantic check itself, would disqualify this query with an Error!
这篇关于如何使用不区分大小写的ASC或DESC，以及DISTINCT和UNION进行订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
