将MySQL的select语句转换为PostgreSQL

Question

我有一个在MySQL中正常工作的查询。更多关于它的背景信息可以在这里找到。

SELECT c.*, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
    WHERE c.id = i.category_id
    AND i.id = v.voteable_id
    AND v.created_at > '#{1.week.ago}'
GROUP BY c.id
ORDER BY score DESC LIMIT 8;

我尝试在PostgreSQL中运行它，但遇到了这个错误消息：

PGError: 错误：列"c.name"必须出现在GROUP BY子句中或用于聚合函数

我不确定这意味着什么，所以我尝试将"group by"子句中的"c.id"更改为"c.name"(假设物品名称唯一，则两者在MySQL中都能正常工作)。

然而，这只是产生了另一个类似的错误：

PGError: 错误：列"c.id"必须出现在GROUP BY子句中或用于聚合函数

如何解决这个问题？

- Brian Armstrong

---

奇怪的是，在Postgre 9.1中似乎现在允许这样做。 - you786

它在PostgreSQL 9.2中被删除了吗？我因为同样的原因收到了这个错误。 - Aaron

@Aaron：它并没有被移除，但只有在（引用文档）：“未分组的列在功能上依赖于分组列，否则将会有多个可能的值返回给未分组的列。如果分组列（或其子集）是包含未分组列的表的主键，则存在功能依赖性。” - Daniel Vérité

Answer 1

你必须在SELECT语句中列出你正在分组的列名：

SELECT c.id, c.name, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
  WHERE c.id = i.category_id
  AND i.id = v.voteable_id
  AND v.created_at > '#{1.week.ago}'
GROUP BY c.id, c.name
ORDER BY score DESC LIMIT 8;

"在SELECT子句中包含未在GROUP BY子句中引用的列名是不允许的。"

- tefozi

---

在MySQL中是允许这样做的，这就是他感到困惑的原因。在我看来，这是一个可怕的特性。 - colithium

Answer 2

我刚遇到过这个问题，是由MySQL转向SQL Server引起的。我认为这种行为能够被允许是很奇怪的！

是的，在大多数数据库中，当你使用GROUP BY子句时，你只能选择列的聚合函数或出现在GROUP BY子句中的列。原因是它无法确定你选择的其他列是否真正唯一。

如果它们确实是唯一的，请将你想要的列放入GROUP BY中。这是MySQL的一个可疑“特性”。

你可以在这里阅读有关MySQL行为及其不同之处的详细信息。

示例：

SELECT c.*, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
    WHERE c.id = i.category_id
    AND i.id = v.voteable_id
    AND v.created_at > '#{1.week.ago}'
GROUP BY c.id, c.name, c.whatever_else
ORDER BY score DESC LIMIT 8;

- colithium

---

感谢提供MySQL链接，现在我更明白了！在这种情况下，我的c.id始终是唯一的，所以可能没问题，但至少我现在知道他们为什么这样做了。谢谢！ - Brian Armstrong

Answer 3

如果你把声明改成这样，它应该可以工作：

SELECT c.id, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
  WHERE c.id = i.category_id
  AND i.id = v.voteable_id
  AND v.created_at > '#{1.week.ago}'
GROUP BY c.id
ORDER BY score DESC LIMIT 8;

我不确定MySQL会给出什么结果，但为了给你一个很小的例子，说明为什么在PostgreSQL中这样做是行不通的，请看下面的categories表：

id | name
---|-----
 1 | ABC
 1 | DEF

你按照id分组，所以结果中每一行应该只包含一个id。如果你也选择了name，但没有按照它进行分组，那么结果中name应该显示什么呢？

它可以是ABC或DEF，但数据库引擎实际上无法为你决定（尽管MySQL显然可以）。

- Ronald Wildenberg

---

MySQL 随机选择一个！这不奇怪吗？我几天前问过这个问题。 - colithium

有趣的是...在这种情况下，至少我的ID始终是唯一的，因此它是一个很好的分组列。我还需要类别的名称，所以我在选择和分组中都列出了它们。看起来可以工作。谢谢！ - Brian Armstrong