在SQLite上很好，在Postgresql:列中必须出现在GROUP子句中，或者在聚合函数中使用

Question

我有一个在SQLite上运行良好的查询，但是当我在Postgresql中的相同数据上运行它时，我得到：

列"role.id“必须出现在GROUP子句中，或用于聚合函数中。

我有三张桌子，供人、展览和一张将两者连接起来的桌子：“一人在一个展览中表演特定角色”(如“艺术家”或“馆长”)：

CREATE TABLE "person" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                       "name" varchar(255));

CREATE TABLE "exhibition" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                           "name" varchar(255));

CREATE TABLE "role" (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                     `name` varchar(30) NOT NULL,
                     `exhibition_id` integer NOT NULL,
                     `person_id` integer NOT NULL, 
                     FOREIGN KEY(`exhibition_id`) REFERENCES `exhibition`(`id`),
                     FOREIGN KEY(`person_id`) REFERENCES `person`(`id`));

我想展示参与展览的人，因为他们做了多少件事。因此，我在一个展览(1,2,3,4)中获得了人员的I，然后执行以下操作：

SELECT
    *,
    COUNT(person.id) AS role_count
FROM person
INNER JOIN role
ON person.id = role.person_id
WHERE person.id IN ( 1, 2, 3, 4 )
GROUP BY person.id
ORDER BY role_count DESC

它通过role_count命令人们，这是他们在所有展览中所扮演的角色的数量

它在SQLite上工作得很好，但在Postgresql中却不行。我尝试过将role.id放入GROUP BY (而不是person.id)，但这改变了结果。

Answer 1

你知道吗，当你挣扎了很久，发布了一个这样的问题，然后马上就发现了答案？

从这个答案中，我意识到我无法选择role.id ( SELECT *正在隐式地选择)，因为它不在GROUP BY中。

我无法将它添加到GROUP BY中(因为这会改变结果)，因此解决方案是不选择它。

因此，我将SELECT部分更改为：

SELECT
    person.*,
    COUNT(person.id) AS role_count
FROM person
...

现在没有选择role.id。而且这很管用。

如果我需要role表中的任何其他字段，比如name，我也可以显式地添加这些字段：

SELECT
    person.*,
    role.name,
    COUNT(person.id) AS role_count
FROM person
...

Answer 2

正如错误说的那样，Standard除了一个SELECT列或对一个聚合函数的调用之外，不允许任何东西。(出于逻辑上的原因:当一个组中有多个行要选择时，关系数据库管理系统如何知道要选择哪个role.id？)PostgreSQL实际上强制执行这条规则；SQLite忽略它，只从组中的任意行返回数据。

正如您所发现的，从role.id中省略SELECT可以修复您的错误。但是，如果您想要SQLite从任意行中选择ID的行为，您可以简单地将它包装在一个聚合函数中，例如，SELECT MAX(role.id)而不仅仅是SELECT role.id)。