GROUP BY 错误：列 "f.path" 必须出现在 GROUP BY 子句中或在聚合函数中使用

Question

在Postgres 12中，我试图在recipes表上执行SELECT，只带来一个图像(文件(。然而，当在没有GROUP BY的情况下执行查询时，我会根据其图像(文件(的数量获得重复的配方。当尝试使用GROUP BY时，我得到以下错误：

列"f.path"；必须出现在GROUP BY子句中或在聚合函数中使用

我正在运行的查询是：

SELECT r.id, r.title, c.name AS chef_name, f.path
FROM recipes AS r
LEFT JOIN chefs AS c ON (r.chef_id = c.id)
LEFT JOIN recipe_files AS rf ON (rf.recipe_id = r.id)
LEFT JOIN files AS f ON (rf.file_id = f.id)
GROUP BY r.id, c.id
ORDER BY r.title ASC

如果我将f.path添加到GROUP BY，我将返回到最初的问题，即根据图像(文件(的数量接收具有重复项目的列表。

Answer 1

如果你只想为每个配方创建一个文件，你可以加入一个只选择一个的派生表：

SELECT r.id, r.title, c.name AS chef_name, f1.path
FROM recipes AS r
LEFT JOIN chefs AS c ON r.chef_id = c.id
LEFT JOIN (
SELECT DISTINCT ON (rf.recipe_id) rf.recipe_id, f.path
FROM recipe_files AS rf 
JOIN files AS f ON rf.file_id = f.id
ORDER BY rf.recipe_id, f.id -- picks an arbitrary file
) f1 ON f1.recipe_id = r.id
ORDER BY r.title ASC

Answer 2

如果每个配方需要一行，则使用distinct on:

SELECT DISTINCT ON (r.title, r.id) r.id, r.title, c.name AS chef_name, f.path
FROM recipes r LEFT JOIN chefs AS c ON (r.chef_id = c.id)
recipe_files AS rf
ON rf.recipe_id = r.id
files f
ON rf.file_id = f.id
ORDER BY r.title, r.id;

Answer 3

我更熟悉SQL Server，但看起来Postgres也有类似的功能。尝试使用ROW_NUMBER((。参考资料如下：https://www.postgresqltutorial.com/postgresql-row_number/

事情会是这样的。您需要对其进行修改以按照自己的意愿进行排序。

SELECT 
r.id
, r.title
, c.name AS chef_name
, f.path
FROM (
SELECT 
r.id
, r.title
, c.name AS chef_name
, f.path
, ROW_NUMBER() OVER(PARTITION BY r.id, r.title, c.name AS chef_name, f.path ORDER BY r.title)
FROM recipes AS r
LEFT JOIN chefs AS c ON (r.chef_id = c.id)
LEFT JOIN recipe_files AS rf ON (rf.recipe_id = r.id)
LEFT JOIN files AS f ON (rf.file_id = f.id)
) 
WHERE row_number = 1;

Answer 4

尽早消除不需要的行。在这种情况下，在加入files:之前应用DISTINCT ON

SELECT r.id, r.title, c.name AS chef_name, f.path
FROM   recipes    r
LEFT   JOIN chefs c ON r.chef_id = c.id
LEFT   JOIN (
SELECT DISTINCT ON (recipe_id)
recipe_id, file_id
FROM   recipe_files
-- without ORDER BY it's truly arbitrary
) rf ON rf.recipe_id = r.id
LEFT   JOIN files f ON rf.file_id = f.id
ORDER  BY r.title;

关于DISTINCT ON:

• 选择每个GROUP BY组中的第一行

查询应该是检索所有配方的最佳查询，而每个配方只有几个文件。

对于每个配方的多个文件，其他技术(快得多(：

• 优化GROUP BY查询以检索每个用户的最新行

只检索几个食谱，但其他技术(更(有效：。类似：

SELECT r.id, r.title, c.name AS chef_name, f.path
FROM   recipes    r
LEFT   JOIN chefs c ON r.chef_id = c.id
LEFT   JOIN LATERAL (
SELECT recipe_id, file_id
FROM   recipe_files
WHERE  recipe_id = r.id
ORDER  BY recipe_id, file_id
LIMIT  1
) rf ON true
LEFT   JOIN files f ON rf.file_id = f.id
WHERE  r.title = 'foo'                     -- some selective filter
ORDER  BY r.title;

参见：

• 选择每个用户前一个月内的数据'；s的最后一条记录