如何在不重複子查詢的情況下進行減法？

我需要有效地獲得：

參加過不止一屆世界杯並且從未獲得過冠軍的球員。

主鍵| 外鍵| pk和fk

PLAYER< player_id , player_name, country_player , …>

世界<年份，獲勝者，…>

PLAYED_WORLD <玩家，世界年，…>

在減法的第一個操作數中，無論他們是否是冠軍，我都會獲得超過 1 個世界杯的球員。在第二個操作數中，我得到了已經贏得超過 1 個世界杯的球員。

以下查詢對我有用，但我的想法是優化它以提高效率，因為兩個操作數之間的區別無非是：WHERE country_player = winner

SELECT *
FROM        (SELECT player_name
       FROM    (SELECT player_id, world_year, player_name, country_player
               FROM    (SELECT player, world_year
                       FROM    (SELECT player
                               FROM played_world
                               GROUP BY player
                               HAVING count(player) > 1) AS playerMoreThan1
                       NATURAL JOIN played_world) AS worldPlayers
               JOIN player ON player= player_id) AS infoWorldPlayers
       JOIN world ON world_year = year) noChampions
EXCEPT      (SELECT player_name
       FROM    (SELECT player_id, world_year, player_name, country_player
               FROM    (SELECT player, world_year
                       FROM    (SELECT player
                               FROM played_world
                               GROUP BY player
                               HAVING count(player) > 1) AS playerMoreThan1
                       NATURAL JOIN played_world) AS worldPlayers
               JOIN player ON player = player_id) AS infoWorldPlayers
       JOIN world ON world_year = year
       WHERE country_player = winner)

是否有重用第一個操作數查詢的策略？

如果您需要出版物中隨附的數據庫的設計和初始負載。

當您有重複子查詢時，您可以使用 CTE。雖然它不一定對性能更好，但在 Postgres 中，CTE 是一個優化圍欄，換句話說，它們被評估一次（或根本不評估）。

您的查詢可能可以更簡單地編寫：

SELECT player
FROM played_world AS pw
WHERE NOT EXISTS
     ( SELECT 1
       FROM world AS w
            JOIN player AS p
            ON w.winner = p.country_player
       WHERE w.year = pw.world_year
         AND p.player_id = pw.player
     ) 
GROUP BY player
HAVING count(*) > 1 ;

或與EXCEPT：

SELECT player
FROM played_world
GROUP BY player
HAVING count(*) > 1 

EXCEPT

SELECT p.player_id
FROM world AS w
    JOIN player AS p
    ON w.winner = p.country_player ;

引用自：https://dba.stackexchange.com/questions/212716