时间戳

首页 > 解决方案 > PostgreSQL GROUP BY 列必须出现在 GROUP BY

sql - PostgreSQL GROUP BY 列必须出现在 GROUP BY

问题描述

SELECT 
COUNT(follow."FK_accountId"),
score.*
FROM
(
    SELECT items.*, AVG(reviews.score) as "averageScore" FROM "ITEM_VARIATION" as items
    INNER JOIN "ITEM_REVIEW" as reviews ON reviews."FK_itemId"=items.id
    GROUP BY items.id
) as score
INNER JOIN "ITEM_FOLLOWER" as follow ON score.id=follow."FK_itemId"
GROUP BY score.id

Inner Block 自己工作，我相信我遵循相同的格式。但是它输出错误：

ERROR:  column "score.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 18:   score.*
           ^

仅列出分数字段中的所有列吗？有超过 10 列要列出，所以如果不是唯一的，我想避免使用该解决方案

标签： sqlpostgresql

解决方案

未包含在聚合中的列必须在期间指定group by

SELECT 
    COUNT(follow."FK_accountId"),
    score.id,
    score.name
FROM
(
    SELECT items.id as id, items.name as name, AVG(reviews.score) as "averageScore" FROM "ITEM_VARIATION" as items
    INNER JOIN "ITEM_REVIEW" as reviews ON reviews."FK_itemId"=items.id
    GROUP BY items.id, items.name
) as score
INNER JOIN "ITEM_FOLLOWER" as follow ON score.id=follow."FK_itemId"
GROUP BY score.id, score.name

推荐阅读