SQL 使用 DISTINCT 关键字会导致此错误：not a SELECTed expression
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15275447/
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
Using the DISTINCT keyword causes this error: not a SELECTed expression
提问by Ken
I have a query that looks something like this:
我有一个看起来像这样的查询：
SELECT DISTINCT share.rooms
FROM Shares share
left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi
... //where clause omitted
ORDER BY share.rooms.floors.floorOrder, share.rooms.roomNumber,
share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent
Which results in the following exception:
这导致以下异常：
Caused by: org.hibernate.exception.SQLGrammarException: ORA-01791: not a SELECTed expression
If I remove the DISTINCT keyword, the query runs without issue. If I remove the order by clause, the query runs without issue. Unfortunately, I can't seem to get the ordered result set without duplicates.
如果我删除 DISTINCT 关键字，查询将毫无问题地运行。如果我删除 order by 子句，查询将毫无问题地运行。不幸的是，我似乎无法获得没有重复的有序结果集。
回答by Lamak
You are trying to order your result with columns that are not being calculated. This wouldn't be a problem if you didn't have the DISTINCTthere, but since your query is basically grouping only by share.roomscolumn, how can it order that result set with other columns that can have multiple values for the same share.roomsone?
您正在尝试使用未计算的列对结果进行排序。如果您没有DISTINCT那里，这不会成为问题，但是由于您的查询基本上仅按share.rooms列分组，它如何对结果集与其他列进行排序，这些列可以具有多个值 share.rooms？
回答by Matthew Zackschewski
This post is a little old but one thing I did to get around this error is wrap the query and just apply the order by on the outside like so.
这篇文章有点旧，但我为解决此错误所做的一件事是包装查询，然后像这样在外部应用订单。
SELECT COL
FROM (
SELECT DISTINCT COL, ORDER_BY_COL
FROM TABLE
// ADD JOINS, WHERE CLAUSES, ETC.
)
ORDER BY ORDER_BY_COL;
Hope this helps :)
希望这可以帮助 ：）
