Блог

Я создал вложенный оператор 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 
 

Последнее FROM утверждение по-прежнему ссылается на исходные таблицы, а не на те, которые определены с помощью этого WITH утверждения.

Если вы измените его с

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

Для

 FROM Player, Team, FullName
 

должно дать вам ожидаемый результат