如何将嵌套的from语句复制到嵌套的with语句

Question

我创建了一个嵌套的from语句，如下所示：

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 (SELECT playerid, teamid, MAX(yearid) as yr, format(avg(salary), 'c') AS avgs
FROM Spring_2021_BaseBall.dbo.Salaries 
GROUP BY playerID, teamid) Player,
(SELECT teamid, yearid, avg(salary) AS teamavg
FROM Spring_2021_BaseBall.dbo.Salaries 
GROUP BY teamid, yearid) Team,
(SELECT playerid,
CONCAT(nameGiven, ' (', nameFirst, ')', ' ', nameLast) as name 
FROM Spring_2021_BaseBall.dbo.People ) Name
WHERE Player.playerid = Name.playerid and 
Player.teamid = Team.teamid and 
Player.yr= Team.yearid
ORDER BY Player.playerid ASC, yearid DESC

我正试图用嵌套的with语句重新创建相同的结果，我尝试过以下操作，但它导致无法绑定多部分标识符。如果我认为我正确命名了每个查询，我该如何解决这个问题？

With Player (playerid, teamid, yearid, 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, Player.teamid, Player.yearid, Player.avgs, Team.teamavg, FullName.name 
FROM Spring_2021_BaseBall.dbo.Salaries, Spring_2021_BaseBall.dbo.People
WHERE Player.playerid = FullName.playerid and 
Player.teamid = Team.teamid and 
Player.yr= Team.yearid
ORDER BY Player.playerid ASC, yearid DESC
go 

Answer 1

最后一个FROM语句仍然引用原始表，而不是用WITH语句定义的表。

如果您将其从更改

FROM Spring_2021_BaseBall.dbo.Salaries, Spring_2021_BaseBall.dbo.People

至

FROM Player, Team, FullName

应该给你预期的结果