mysql - 将 MySQL 选择转换为 PostgreSQL

Question

我有一个在 MySQL 中可以正常工作的查询。更多背景信息 here .

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: ERROR: column "c.name" must appear in the GROUP BY clause or be used in an aggregate function

我不确定这是什么意思，所以我尝试将 group by 子句中的“c.id”更改为“c.name”(两者在 MySQL 中的工作方式相同，假设项目的名称是唯一的)。

然而这又产生了另一个类似的错误

PGError: ERROR: column "c.id" must appear in the GROUP BY clause or be used in an aggregate function

如何解决这个问题？

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 子句中未引用的列名。”