使用DISTINCT关键字会导致此错误：不是SELECTed表达式 [英] Using the DISTINCT keyword causes this error: not a SELECTed expression

问题描述

我有一个如下所示的查询：

  SELECT DISTINCT share.rooms 
 FROM Shares share 
 left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi 
 ... // where子句省略
 ORDER BY share.rooms.floors.floorOrder，share.rooms.roomNumber，
 share.rooms.firstEffectiveAt，share.shareNumber，share.sharePercent 
  

其中导致以下异常：

pre $ 原因：org.hibernate.exception.SQLGrammarException：ORA-01791：不是一个SELECTed表达式
code>

如果我删除了DISTINCT关键字，查询运行没有问题。如果我删除order by子句，查询运行没有问题。不幸的是，我似乎无法得到没有重复的有序结果集。

解决方案

您试图将结果与未计算的列排序。如果您没有 DISTINCT 那么这不会成为问题，但是由于您的查询基本上只能由 share.rooms >列，它如何将结果集与其他可以具有多个值的结果集排序为相同的 share.rooms 一个？

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.

解决方案

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?

这篇关于使用DISTINCT关键字会导致此错误：不是SELECTed表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！