PostgreSQL中SELECT DISTINCT使用报错及重复数据问题咨询

报错原因 #

PostgreSQL 规定：使用 SELECT DISTINCT 时，ORDER BY 子句中的所有字段必须出现在 SELECT 的查询列中。这是因为去重后，同一film.name可能对应多条不同year的记录，数据库无法确定用哪条记录的year来排序。

解决方案 #

方案1：将ORDER BY字段加入SELECT列表 #

把year添加到查询列中，同时保留DISTINCT，这样数据库能明确每个去重后记录的排序依据：

SELECT DISTINCT film.name, year 
FROM film
INNER JOIN participation ON film.cod_film = participation.cod_film
WHERE year < 2000 AND professional.name = 'Kenneth Branagh'
ORDER BY year ASC;

方案2：使用PostgreSQL特有的DISTINCT ON语法 #

如果不想在结果中显示year，可以用DISTINCT ON指定按film.name去重，同时通过ORDER BY先按去重字段排序，再按year排序，确保每个电影名保留最早年份的记录：

SELECT DISTINCT ON (film.name) film.name
FROM film
INNER JOIN participation ON film.cod_film = participation.cod_film
WHERE year < 2000 AND professional.name = 'Kenneth Branagh'
ORDER BY film.name, year ASC;

方案3：用GROUP BY替代DISTINCT实现去重 #

通过GROUP BY film.name合并重复的电影记录，再用聚合函数MIN(year)获取每个电影的最早年份作为排序依据：

SELECT film.name
FROM film
INNER JOIN participation ON film.cod_film = participation.cod_film
WHERE year < 2000 AND professional.name = 'Kenneth Branagh'
GROUP BY film.name
ORDER BY MIN(year) ASC;

内容的提问来源于stack exchange，提问作者bakun