使用 DISTINCT 关键字会导致此错误:not a SELECTed expression

2021-12-30 00:00:00 sql oracle11g oracle Hibernate

我有一个类似这样的查询:

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

导致以下异常:

Caused by: org.hibernate.exception.SQLGrammarException: ORA-01791: not a SELECTed expression

如果我删除 DISTINCT 关键字，查询将正常运行.如果我删除 order by 子句，查询将毫无问题地运行.不幸的是，我似乎无法获得没有重复的有序结果集.

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 ，这不会成为问题，但是由于您的查询基本上仅按 share.rooms 列分组，因此如何对其进行排序结果集与其他列可以具有相同 share.rooms 的多个值?

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 DISTINCT there, but since your query is basically grouping only by share.rooms column, how can it order that result set with other columns that can have multiple values for the same share.rooms one?

相关文章