错误: SELECTDISTINCT，ORDERBY 表达式必须出现在选择列表中

ActionView: : Template: : ERROR (PG: : ERROR: ERROR: 对于 SELECT DISTINCT，ORDER BY 表达式必须出现在选择列表中

我正在创建一个活动网站，我正在尝试按照活动开始的时间对呈现的回复进行排序。有很多 RSVPS，所以我把它们分成不同的组，但是在过去的几天里，我在排序结果时遇到了很多困难，没有这个错误出现在 PG 上。我已经看了一些以前关于这个话题的问题，但还是很迷茫。我怎么才能让这个起作用？非常感谢！

@rsvps = Rsvp.where(:voter_id => current_user.following.collect {|f| f["id"]}, :status => 'going').where("start_time > ? AND start_time < ?", Time.now, Time.now + 1.month).order("count_all desc").count(:group => :event_id).collect { |f| f[0] }


<%= render :partial => 'rsvps/rsvp', :collection => Rsvp.where(:event_id => @rsvps).select("DISTINCT(event_id)").order('start_time asc') %>
118608 次浏览
小开
最佳答案

ORDERBY 子句只能应用于应用了 DISTINCT 的 之后。因为只有 SELECT 语句中的字段被考虑用于 DISTINCT 操作，所以 ORDERBY 中只能使用这些字段。

从逻辑上讲，如果您只想要一个单独的 event _ id 值列表，那么它们的出现顺序应该是无关紧要的。如果顺序很重要，那么应该将 start _ time 添加到 SELECT 列表中，以便存在订单的上下文。

另外，这两个 SELECT 子句不是等价的，所以要小心:

SELECT DISTINCT(event_id, start_time) FROM ...


SELECT DISTINCT event_id, start_time FROM ...

第二个是您想要的形式。第一个函数将返回一系列记录，其中的数据表示为 ROW 构造(一个包含元组的列)。第二个将返回数据输出的正常列。它只在单列情况下工作，这种情况下 ROW 结构被减少，因为它只是一个单列。

小开

我知道这是一个相当古老的问题，但我只是通过我的头脑中的一个小例子，帮助我了解为什么 Postgres 有这个看似奇怪的限制 SELECT DISTINCT/ORDER BY 列。

假设您的 Rsvp 表中包含以下数据:

 event_id |        start_time
----------+------------------------
0     | Mar 17, 2013  12:00:00
1     |  Jan 1, 1970  00:00:00
1     | Aug 21, 2013  16:30:00
2     |  Jun 9, 2012  08:45:00

现在，您需要获取一个不同的 event _ ids 列表，按照它们各自的 start _ times 进行排序。但是 1应该去哪里呢？它应该首先出现，因为一个元组在1970年1月1日开始，还是应该最后出现，因为2013年8月21日？

由于数据库系统不能为您做出决定，而且查询的语法也不能依赖于它可能操作的实际数据(假设 event_id是唯一的) ，因此我们只能根据 SELECT子句中的列进行排序。

至于实际的问题—— Matthew 的答案的一个替代方案是使用聚合函数(如 MIN或 MAX)进行排序:

  SELECT event_id
FROM Rsvp
GROUP BY event_id
ORDER BY MIN(start_time)

start_time上的显式分组和聚合允许数据库对结果元组进行明确的排序。但是请注意，在这种情况下，可读性肯定是一个问题;)

小开

因为您正在使用 start _ time 列，所以可以使用 row _ number () ，它是 PostgreSQL 的窗口函数中的一个，并将其堆栈在其中

  • 如果期望第一个 start _ time 的行值，则按 start _ time 的顺序排列

    选择 event _ id from (SELECT event _ id ，ROW _ NUMBER () OVER (PARTITION BY event _ id ORDER BY start _ time)作为 first _ ROW 其中 first _ row = 1

  • 如果期望最后一个 start _ time 的行值，则逆序排列 start _ time

    选择 event _ id from (SELECT event _ id ，ROW _ NUMBER () OVER (PARTITION BY event _ id ORDER BY start _ time desc)作为 last _ ROW 其中 last _ row = 1

您还可以根据需要使用不同的 窗口功能。

小开

我能够通过在我的选择中添加一列，然后在该列上使用 ORDER BY来解决这个错误。我有 SELECT DISTINCT concat(dl.FirstName, concat(' ', dl.LastName)) as displayName, ...和我想 ORDER BY姓(作为一个)。我尝试了所有我能想到的在 SELECT中添加 ORDER BY的排列方式，但最有效的方式是像在更典型的查询中那样，在查询的末尾添加 , dl.LastName as lastName，然后再添加 ORDER BY lastName ASC。

我最终添加了一个额外的专栏，但是在我的应用程序中很容易忽略它。

小开

操作的语法顺序与逻辑顺序

我认为围绕着 ABC0和 ORDER BY之间的关系(或者也是 GROUP BY，就此而言)的混淆，只有在 SQL 中操作的逻辑顺序被理解的情况下才能真正被理解。它不同于操作的句法顺序，操作是造成混淆的主要原因。

在这个例子中，考虑到它的语法紧密性，它就像 DISTINCT与 SELECT有关一样，但它实际上是一个应用 之后 SELECT(投影)的操作符。由于 DISTINCT的工作性质(删除重复的行) ，一行中所有未投影的内容都不再可用 之后 DISTINCT操作，其中包括 ORDER BY子句。根据 操作的逻辑顺序(简化):

  • FROM(产生所有可能的列引用)
  • WHERE(可以使用来自 FROM的所有列引用)
  • SELECT(可以使用来自 FROM的所有列引用，并创建新的表达式和别名)
  • DISTINCT(操作由 SELECT投影的元组)
  • ORDER BY(取决于 DISTINCT的存在，可以对 SELECT投影的元组进行操作，如果 DISTINCT不存在 * 也许(取决于方言)也可以对其他表达式进行操作)

那 DISTINCT和 ORDER BY呢

事实上，如果没有 DISTINCT，ORDER BY也可以访问(在一些方言中)还没有投影的东西，这可能有点奇怪，当然是有用的。例如:

WITH emp (id, fname, name) AS (
VALUES (1, 'A', 'A'),
(2, 'C', 'A'),
(3, 'B', 'B')
)
SELECT id
FROM emp
ORDER BY fname DESC

生产

id
--
2
3
1

当你添加 DISTINCT时，这个改变将不再有效:

WITH emp (id, fname, name) AS (
VALUES (1, 'A', 'A'),
(2, 'C', 'A'),
(3, 'B', 'B')
)
SELECT DISTINCT name
FROM emp
ORDER BY fname DESC

Dbfiddle here 错误是:

错误: 对于 SELECTDISTINCT，ORDERBY 表达式必须出现在选择列表中 第8行: ORDBY fname DESC

因为什么样的 fname值你会归因于 name = A？A还是 C？答案将决定你是否会得到 A，B作为一个结果或 B，A。无法决定。

PostgreSQL DISTINCT ON

现在，正如在 以上连结文章中提到的，PostgreSQL 支持这个异常，这个异常偶尔会有用: DISTINCT ON(也参见 像这样的问题) :

WITH emp (id, fname, name) AS (
VALUES (1, 'A', 'A'),
(2, 'C', 'A'),
(3, 'B', 'B')
)
SELECT DISTINCT ON (name) id, fname, name
FROM emp
ORDER BY name, fname, id

Dbfiddle here ，制作:

id |fname|name
---|-----|----
1  |A    |A
3  |B    |B

这个查询只允许生成不同的 name值，然后在给定 ORDER BY子句的情况下，对每个重复行采用第一个值，这使得对每个不同组的选择没有歧义。这可以在其他 RDBMS 中使用窗口函数进行模拟.


提交答案