将MySQL select转换为PostgreSQL [英] Converting MySQL select to PostgreSQL

问题描述

我有这个查询，可以在MySQL中正常工作. 此处.

I have this query which works correctly in MySQL. More background on it 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中运行它，但由于出现此错误消息而失败.

I tried running it in PostgreSQL, and it failed with this error message.

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

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中，两者都是相同的，假定项的名称是唯一的).

I wasn't sure what this meant, so I tried changing "c.id" to "c.name" in the group by clause (both work in MySQL the same, assuming the name of an item is unique).

但是这只是产生了另一个类似的错误

However this just produced another similar error

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

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

该问题如何解决?

推荐答案

您必须在要分组的SELECT中列出列名称:

You have to list column names in SELECT which you are grouping in:

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

"It is not permissible to include column names in a SELECT clause that are not referenced in the GROUP BY clause."

这篇关于将MySQL select转换为PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！