我的第二个内部加入在此语句中有什么问题

Question

此查询作品：

select  rc.[race number],
    max(case when seqnum = 1 then [candidate num] end) as Winner,
    max(case when seqnum = 1 then Votes end) as WinningVotes,
    max(case when seqnum = 1 then party end) as WinningParty,
    max(case when seqnum = 2 then [candidate num] end) as Loser,
    max(case when seqnum = 2 then Votes end) as LosingVotes,
    max(case when seqnum = 2 then party end) as LosingParty
from 
(
    select  rc.[race number],
            rc.[candidate num],
            rc.[Votes],
            c.[party],
                row_number() over (partition by rc.[race number] order by votes desc) as seqnum
        from    dbo.[RACE CANDIDATES] rc
        inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
) rc
group by rc.[race number]

现在，我需要加入一个名为Race的第三个表中的信息。我已经尝试了以下查询，但是在第二个内部加入之前，它在我的语句中出现了错误：

select  rc.[race number]
    max(case when seqnum = 1 then [candidate num] end) as Winner,
    max(case when seqnum = 1 then Votes end) as WinningVotes,
    max(case when seqnum = 1 then party end) as WinningParty,
    max(case when seqnum = 2 then [candidate num] end) as Loser,
    max(case when seqnum = 2 then Votes end) as LosingVotes,
    max(case when seqnum = 2 then party end) as LosingParty
from 
(
    select  rc.[race number],
            rc.[candidate num],
            rc.[Votes],
            c.[party],
                row_number() over (partition by rc.[race number] order by votes desc) as seqnum
        from    dbo.[RACE CANDIDATES] rc
        inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
        from    dbo.[RACE] r
        inner join dbo.[RACE CANDIDATES] on rc.[race number] = r.[race number]          
) rc
group by rc.[race number]

我不确定我在这里做错了什么。这甚至可能吗？我知道我已经在此特定查询上发布了很多，但是我只想正确。预先感谢您的有价值的帮助。

Answer 1

更改查询如下

select  rc.[race number]
    max(case when seqnum = 1 then [candidate num] end) as Winner,
    max(case when seqnum = 1 then Votes end) as WinningVotes,
    max(case when seqnum = 1 then party end) as WinningParty,
    max(case when seqnum = 2 then [candidate num] end) as Loser,
    max(case when seqnum = 2 then Votes end) as LosingVotes,
    max(case when seqnum = 2 then party end) as LosingParty
from 
(
select  rc.[race number],
        rc.[candidate num],
        rc.[Votes],
        c.[party],
            row_number() over (partition by rc.[race number] order by votes desc) as seqnum
    from    dbo.[RACE CANDIDATES] rc
    inner join dbo.[CANDIDATE] c    on  rc.[candidate num]  = c.[candidate number]
    inner join dbo.[RACE] r
     on rc.[race number] = r.[race number]          
) rc
group by rc.[race number]