PostgreSQL临时表查询去重：如何避免结果出现重复行？

你的查询出现大量重复行的核心原因是：film CTE返回的是符合条件的所有电影行（每个age_rating对应多条记录），而duration CTE是按age_rating分组的聚合结果（每个age_rating仅1条记录）。两者关联时，每个age_rating的聚合结果会和film中对应age_rating的每一行重复匹配，最终输出大量重复的聚合数据。

方案1：直接查询聚合结果（最简洁） #

既然你最终查询的所有字段都来自duration CTE，完全不需要film CTE参与，直接查询duration即可，结果自然无重复：

WITH duration AS (
    SELECT
        f.rating as age_rating,
        MIN(f.length) AS min_length,
        MAX(f.length) AS max_length,
        AVG(f.length) AS avg_length,
        MIN(f.rental_rate) AS min_rental_rate,
        MAX(f.rental_rate) AS max_rental_rate,
        AVG(f.rental_rate) AS avg_rental_rate
    FROM movie AS f
    WHERE f.rental_rate > 2  -- 把原film的过滤条件移到这里，保证聚合范围一致
    GROUP BY age_rating  
    ORDER BY avg_length ASC
)
SELECT * FROM duration;

注意：我把原film中的rental_rate > 2条件移到了duration的查询里，确保聚合的是符合条件的电影数据，和原逻辑一致。

方案2：保留CTE结构但去重（如果需要后续扩展film字段） #

如果后续需要在结果中加入film里的字段，可先对film CTE按age_rating去重，避免重复匹配：

WITH film AS (
    SELECT DISTINCT ON (m.rating)
        m.rating AS age_rating
    FROM movie AS m      
    WHERE m.rental_rate >2  
),
duration AS (
    SELECT
        f.rating as age_rating,
        MIN(f.length) AS min_length,
        MAX(f.length) AS max_length,
        AVG(f.length) AS avg_length,
        MIN(f.rental_rate) AS min_rental_rate,
        MAX(f.rental_rate) AS max_rental_rate,
        AVG(f.rental_rate) AS avg_rental_rate
    FROM movie AS f
    WHERE f.rental_rate >2
    GROUP BY age_rating  
    ORDER BY avg_length ASC
)
SELECT 
    film.age_rating,
    duration.min_length,
    duration.max_length,
    duration.avg_length,
    duration.min_rental_rate,
    duration.max_rental_rate,
    duration.avg_rental_rate
FROM film INNER JOIN duration ON film.age_rating = duration.age_rating;

这里用DISTINCT ON (m.rating)让film中每个age_rating仅保留1条记录，关联后自然不会产生重复行。

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