Oracle PL/SQL Query Order By 问题与 Distinct
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/243510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL Query Order By issue with Distinct
提问by Chris Conway
Does anyone know what is wrong with this query?
有谁知道这个查询有什么问题？
SELECT DISTINCT c.CN as ClaimNumber,
a.ItemDate as BillReceivedDate, c.DTN as
DocTrackNumber
FROM ItemData a,
ItemDataPage b,
KeyGroupData c
WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
AND a.ItemNum = b.ItemNum
AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC;
I have done T-Sql most of my career and this looks correct to me, however this query is for an Oracle database and Toad just places the cursor on the a.DateStored in the Order By section. I'm sure this is elementary for anyone doing PL/SQL.
我在职业生涯的大部分时间都做过 T-Sql，这对我来说似乎是正确的，但是这个查询是针对 Oracle 数据库的，而 Toad 只是将光标放在 Order By 部分的 a.DateStored 上。我相信这对于任何使用 PL/SQL 的人来说都是基本的。
Thanks!
谢谢！
[EDIT] For future reference, the error given by SQL*Plus was: "ORA-01791: not a SELECTed expression"
[编辑] 为了将来参考，SQL*Plus 给出的错误是：“ORA-01791: not a SELECTed expression”
回答by Brian Schmitt
You will need to modify the query as such:
您需要像这样修改查询：
SELECT DISTINCT c.CN as ClaimNumber,
a.ItemDate as BillReceivedDate, c.DTN as
DocTrackNumber, a.DateStored
FROM ItemData a,
ItemDataPage b,
KeyGroupData c
WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
AND a.ItemNum = b.ItemNum
AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC;
When doing a DISTINCT your order by needs to be one of the selected columns.
执行 DISTINCT 时，您的 order by 需要是选定的列之一。
回答by Chris Conway
Nevermind, executing in SQL Plus gave me a more informative answer. The DateStored needs to be in the select statement so this works:
没关系，在 SQL Plus 中执行给了我更多信息的答案。DateStored 需要在 select 语句中，这样才能工作：
SELECT DISTINCT c.CN as ClaimNumber,
a.ItemDate as BillReceivedDate,
c.DTN as DocTrackNumber,
a.DateStored
FROM ItemData a,
ItemDataPage b,
KeyGroupData c
WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
AND a.ItemNum = b.ItemNum
AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC;
回答by Chris Conway
I believe that the elements of the order by clause must also be in the select clause.
我认为 order by 子句的元素也必须在 select 子句中。
