子选择列无效问题

Question

我在子选择中遇到列名称的 sql 问题，我猜是因为它尚未分配该名称，但我无法弄清楚如何重新排列它。

select Distinct Captains.Name, Captains.Team, (select count(Winners.Name) from (select HomeTeamCaptain As Name from fixture where fixture.HomeTeamCaptain = Captains.Name And fixture.matchResult = fixture.HomeTeam UNION ALL select AwayTeamCaptain As Name from fixture where fixture.AwayTeamCaptain = Captains.Name And fixture.matchResult = fixture.AwayTeam) As Winners) As Winners From (select fixture.HomeTeamCaptain As Name, HomeTeam As Team From fixture UNION ALL select fixture.AwayTeamCaptain As Name, AwayTeam As Team From fixture) As Captains order by Name;

“Captains.Name”是我需要它来运行计数 - 子选择但无法获取其值的问题！

Answer 1

这对我有用：

select distinct Captains.Name, Captains.Team, 
    (select count(*)  
     from fixture 
     where 
        --I changed this a bit
        (fixture.HomeTeamCaptain = Captains.Name And
         fixture.matchResult = fixture.HomeTeam) 
     OR
        (fixture.AwayTeamCaptain = Captains.Name And 
         fixture.matchResult = fixture.AwayTeam)
    ) As Winners

FROM (select fixture.HomeTeamCaptain As Name, HomeTeam As Team 
     from fixture 
     UNION ALL 
     select fixture.AwayTeamCaptain As Name, AwayTeam As Team 
     from fixture) AS Captains
order by Captains.Name;

您应该考虑创建一个 Captains 表并通过 Id 引用“Home”和“Away”队长。