mysql select 转换,将MySQL select转换为PostgreSQL

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;

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

PGError: ERROR: column "c.name" must

appear in the GROUP BY clause or be

used in an aggregate function

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: ERROR: column "c.id" must

appear in the GROUP BY clause or be

used in an aggregate function

How can this problem be resolved?

解决方案

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;

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