SQL 在另一个 SELECT 中使用 SELECT 结果

Using SELECT result in another SELECT

提问by Pandepic

So here is my query

所以这是我的查询

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;

The query is to give a result set for a GridView based leaderboard and what I want is to only get the average of Scores that are less than 3 months old. I have this in 2 parts as you can see and obviously it gives an error like this.

查询是为基于 GridView 的排行榜提供结果集，我想要的是只获得不到 3 个月的分数的平均值。如您所见，我将其分为两部分，显然它会出现这样的错误。

Msg 4104, Level 16, State 1, Line 2

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

消息 4104，级别 16，状态 1，第 2 行

NewScores.NetScore无法绑定多部分标识符“ ”。

Which is because of this AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett

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

How do I make it so that I can use NewScoresthere so I'm only getting the average of the scores less than 3 months old?

我如何制作它以便我可以NewScores在那里使用，所以我只能得到不到 3 个月的分数的平均值？

EDIT: Using the answers people provided I've solved it by using a join in the correct place and here is the correct query:

编辑：使用人们提供的答案我已经通过在正确的位置使用连接解决了它，这里是正确的查询：

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

回答by John D

NewScores is an alias to Scores table - it looks like you can combine the queries as follows:

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;

回答by Dinesh

What you are looking for is a query with WITHclause, if your dbms supports it. Then

WITH如果您的 dbms 支持，您正在寻找的是带有子句的查询。然后

WITH NewScores AS (
    SELECT * 
    FROM Score  
    WHERE InsertedDate >= DATEADD(mm, -3, GETDATE())
)
SELECT 
<and the rest of your query>
;

Note that there is no ;in the first half. HTH.

请注意，;上半场没有。哈。

回答by sashkello

You are missing table NewScores, so it can't be found. Just join this table.

您缺少 table NewScores，因此无法找到它。只需加入这张桌子。

If you really want to avoid joining it directly you can replace NewScores.NetScorewith SELECT NetScore FROM NewScores WHERE {conditions on which they should be matched}

如果你真的想避免直接加入它，你可以替换NewScores.NetScore为 SELECT NetScore FROM NewScores WHERE {conditions on which they should be matched}