如何将SQL嵌套FROM语句改写为嵌套WITH语句并解决绑定报错

• 列名不匹配：你定义Player CTE时将MAX(yearid)对应的列名指定为yearid，但后续WHERE条件中调用的是Player.yr，该字段不存在导致绑定失败，和你原嵌套FROM写法里的yr别名没有对应。
• FROM子句引用错误：你已经定义了Player、Team、FullName三个CTE作为临时结果集，但主查询的FROM子句仍引用原始业务表Spring_2021_BaseBall.dbo.Salaries和Spring_2021_BaseBall.dbo.People，没有引用你定义的CTE，自然无法识别CTE的字段。
• 输出字段不匹配：原查询的字段别名、格式转换、差值计算逻辑你都没有在WITH版本的主查询中复现，结果会和需求不一致。

WITH Player (playerid, teamid, yr, avgs)  AS (
    SELECT playerid, teamid, MAX(yearid), FORMAT(AVG(salary), 'C') AS avgs 
    FROM Spring_2021_BaseBall.dbo.Salaries 
    GROUP BY playerid, teamid
), 
Team (teamid, yearid, teamavg) AS (
    SELECT teamid, yearid, AVG(salary) AS teamavg
    FROM Spring_2021_BaseBall.dbo.Salaries 
    GROUP BY teamid, yearid
), 
FullName (playerid, name) AS (
    SELECT playerid, CONCAT(nameGiven, ' (', nameFirst, ')', ' ', nameLast) AS name 
    FROM Spring_2021_BaseBall.dbo.People
)
SELECT 
    Player.playerid, 
    name AS [Full Name], 
    avgs AS [Player Average], 
    FORMAT(teamavg, 'C') AS [Team Average],
    Player.teamid, 
    FORMAT((Team.teamavg - Player.avgs), 'C') AS [Difference], 
    yr AS [Last Year]
FROM Player, Team, FullName
WHERE 
    Player.playerid = FullName.playerid 
    AND Player.teamid = Team.teamid 
    AND Player.yr = Team.yearid
ORDER BY Player.playerid ASC, yearid DESC
GO

内容的提问来源于stack exchange，提问作者deepvalue