我正在使用Union All进行查询，并希望确保向结果查询添加重复的行。使用Union All时，未正确添加重复行。在google搜索“sql union with duplicates”时，它声明：“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
提前感谢您的帮助！
最佳答案
正如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
关于sql - SQL-使用并集并添加重复项，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40581011/