时间戳

首页 > 解决方案 > SQL 相交嵌套查询

sql - SQL 相交嵌套查询

问题描述

使用 Oracle SQL，我正在尝试编写一个语句来回答以下问题： NFL 球队的胜场数高于分区平均数，但罚球码数也多于分区平均数。

我有两个单独的语句，可以单独返回每个语句（即：一个列出胜率高于分区平均数的球队的表格，以及一个列出罚球码数高于平均数的球队的单独表格）

但是，当我尝试与这两个查询相交时，我遇到了错误。“缺少选择关键字”是给定的错误。

WITH divisionPenaltyYards AS 
(
    SELECT division
        ,avg(penaltyyards) AS AVGPenalty
    FROM nfl.teams
    GROUP BY division
)
WITH divisionWins AS 
(
        SELECT division
            ,avg(wins) AS AVGWins
        FROM nfl.teams
        GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionPenaltyYards 
    ON nfl.teams.division = divisionPenaltyYards.division
WHERE penaltyyards > AVGPenalty

INTERSECT

SELECT team
FROM nfl.teams
INNER JOIN divisionWins 
    ON nfl.teams.division = divisionWins.division
WHERE wins > AVGWins;

编辑：每个请求的更多信息

NFL.Teams 包含 NFL 中所有球队的统计数据。球队、分区、胜利、罚球……

数据集的一个示例将包括 Packers, NFC NORTH, 6, 984 Steelers, AFC NORTH, 12, 817 等等。

所需的结果将是一个表格，列出所有胜场数高于分区平均数，同时罚球码数也多于分区平均数的球队。

标签： sqloracle

解决方案

第二个 CTE 不应以WITH关键字开头。只是, divisionWins AS 

WITH divisionPenaltyYards AS 
(
    SELECT division
        ,avg(penaltyyards) AS AVGPenalty
    FROM nfl.teams
    GROUP BY division
)
, divisionWins AS 
(
        SELECT division
            ,avg(wins) AS AVGWins
        FROM nfl.teams
        GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionPenaltyYards 
    ON nfl.teams.division = divisionPenaltyYards.division
WHERE penaltyyards > AVGPenalty

INTERSECT

SELECT team
FROM nfl.teams
INNER JOIN divisionWins 
    ON nfl.teams.division = divisionWins.division
WHERE wins > AVGWins;

这里也不需要使用两个 CTE。

WITH divisionCTE AS 
(
    SELECT division, 
        avg(penaltyyards) as avgpenalty, 
        avg(wins) as avgwins 
    FROM nfl.teams 
    GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE penaltyyards > AVGPenalty

INTERSECT

SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE wins > AVGWins;

此外，INTERSECT 在这里工作，但 UNION 会更有意义。最终，两者都不需要：

WITH divisionCTE AS 
(
    SELECT division, 
        avg(penaltyyards) as avgpenalty, 
        avg(wins) as avgwins 
    FROM nfl.teams 
    GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE penaltyyards > AVGPenalty
    AND wins > avgwins

最后，如果你想避免 JOIN 你可以使用窗口函数：

SELECT team
FROM 
    (
        SELECT team, wins, penaltyyards,
            avg(penaltyyards) OVER (PARTITION BY division) as avgpenalty, 
            avg(wins) OVER (PARTITION BY division) as avgwins 
        FROM nfl.teams 
    ) averages
WHERE penaltyyards > AVGPenalty AND wins > avgwins

推荐阅读