时间戳

首页 > 解决方案 > 如何将嵌套的语句复制到嵌套的 with 语句？

sql - 如何将嵌套的语句复制到嵌套的 with 语句？

问题描述

我创建了一个嵌套的 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

标签： sqlnestedwith-statement

解决方案

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

如果你把它从

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

至

FROM Player, Team, FullName

应该给你预期的结果

推荐阅读