SQL - 使用 Union All 并添加重复项

Question

我正在使用 Union All 进行查询，我想确保将重复的行添加到结果查询中。使用 Union All 时，不会正确添加重复行。当在 google 上搜索“带重复项的 sql 联合”时，它指出:“联合会删除重复项。联合所有不会。您在查询的第一部分还没有重复项(可能由左连接生成)。据我了解 UNION 它不会添加到结果集中已经存在的行，但它不会删除第一个数据集中已经存在的重复项。”就我而言，我确实想添加其中已有的结果集行。我该怎么做？

这是我的查询:

SELECT player_name_fkey, school_name_fkey, score_simple, sum(score_simple) over (partition by player_name_fkey) as sum_scores

FROM (

    SELECT player_name_fkey, school_name_fkey, SUM(score) as score_simple 

        FROM simple LEFT JOIN players on players.name=simple.player_name_fkey 

        WHERE category='Cadet' AND gender='Féminin' GROUP BY player_name_fkey, school_name_fkey

    UNION ALL

        SELECT player_name_fkey, school_name_fkey, SUM(score) as score_simple

        FROM double LEFT JOIN players on players.name=double.player_name_fkey 

        WHERE category='Cadet' AND gender='Féminin' GROUP BY player_name_fkey, school_name_fkey) t1

GROUP BY player_name_fkey, school_name_fkey, score_simple

在此先感谢您的帮助!

Answer 1

如 Felix Pamittan 所述，在末尾删除 GROUP BY 子句解决了我的问题。

SELECT player_name_fkey, school_name_fkey, score_simple, sum(score_simple) over (partition by player_name_fkey) as sum_scores

FROM (

    SELECT player_name_fkey, school_name_fkey, SUM(score) as score_simple 

    FROM simple LEFT JOIN players on players.name=simple.player_name_fkey 

    WHERE category='Cadet' AND gender='Féminin' GROUP BY player_name_fkey, school_name_fkey

UNION ALL

    SELECT player_name_fkey, school_name_fkey, SUM(score) as score_simple

    FROM double LEFT JOIN players on players.name=double.player_name_fkey 

    WHERE category='Cadet' AND gender='Féminin' GROUP BY player_name_fkey, school_name_fkey) t1