sql - 在另一个 SELECT 中使用 SELECT 结果

Question

这是我的查询

SELECT 
    * 
FROM 
    Score  AS NewScores 
WHERE 
    InsertedDate >= DATEADD(mm, -3, GETDATE());


SELECT 
    ROW_NUMBER() OVER( ORDER BY NETT) AS Rank, 
    Name, 
    FlagImg, 
    Nett, 
    Rounds 
FROM (
    SELECT 
        Members.FirstName + ' ' + Members.LastName AS Name, 
        CASE 
            WHEN MenuCountry.ImgURL IS NULL THEN 
                '~/images/flags/ismygolf.png' 
            ELSE 
                MenuCountry.ImgURL 
        END AS FlagImg, 
        AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, 
        COUNT(Score.ScoreID) AS Rounds 
    FROM 
        Members 
        INNER JOIN 
        Score 
            ON Members.MemberID = Score.MemberID 
        LEFT OUTER JOIN MenuCountry 
            ON Members.Country = MenuCountry.ID 
    WHERE 
        Members.Status = 1 
    GROUP BY 
        Members.FirstName + ' ' + Members.LastName, 
        MenuCountry.ImgURL
    ) AS Dertbl 
ORDER BY;
    

该查询是为基于 GridView 的排行榜提供结果集，而我想要的是仅获取 3 个月以内的分数的平均值。正如您所看到的，我将其分为两部分，显然它给出了这样的错误。

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "NewScores.NetScore" could not be bound.

这是因为这个 AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett

如何才能在那里使用 NewScores 这样我只能得到 3 个月内分数的平均值？

编辑:使用人们提供的答案，我通过在正确的位置使用联接解决了这个问题，这是正确的查询:

SELECT ROW_NUMBER() OVER(ORDER BY NETT) AS Rank, Name, FlagImg, Nett, Rounds FROM (SELECT Members.FirstName + ' ' + Members.LastName AS Name, CASE WHEN MenuCountry.ImgURL IS NULL THEN '~/images/flags/ismygolf.png' ELSE MenuCountry.ImgURL END AS FlagImg, AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, COUNT(NewScores.ScoreID) AS Rounds FROM Members INNER JOIN (SELECT * FROM Score WHERE InsertedDate >= DATEADD(mm, -5, GETDATE())) NewScores ON Members.MemberID = NewScores.MemberID LEFT OUTER JOIN MenuCountry ON Members.Country = MenuCountry.ID WHERE Members.Status = 1 GROUP BY Members.FirstName + ' ' + Members.LastName, MenuCountry.ImgURL) AS Dertbl ORDER BY Nett ASC

Answer 1

NewScores 是 Scores 表的别名 - 看起来您可以按如下方式组合查询:

SELECT 
    ROW_NUMBER() OVER( ORDER BY NETT) AS Rank, 
    Name, 
    FlagImg, 
    Nett, 
    Rounds 
FROM (
    SELECT 
        Members.FirstName + ' ' + Members.LastName AS Name, 
        CASE 
            WHEN MenuCountry.ImgURL IS NULL THEN 
                '~/images/flags/ismygolf.png' 
            ELSE 
                MenuCountry.ImgURL 
        END AS FlagImg, 
        AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, 
        COUNT(Score.ScoreID) AS Rounds 
    FROM 
        Members 
        INNER JOIN 
        Score NewScores
            ON Members.MemberID = NewScores.MemberID 
        LEFT OUTER JOIN MenuCountry 
            ON Members.Country = MenuCountry.ID 
    WHERE 
        Members.Status = 1 
        AND NewScores.InsertedDate >= DATEADD(mm, -3, GETDATE())
    GROUP BY 
        Members.FirstName + ' ' + Members.LastName, 
        MenuCountry.ImgURL
    ) AS Dertbl 
ORDER BY;