内部联接选择语句以按日期查找下一条记录

Question

我一直在努力研究如何将一个select语句内部联接到另一个select声明，以便按日期顺序返回特定项的下一个值。我有很多表格可以识别比赛会议、比赛和参赛者，还有一个初始选择，可以识别赢得第一场比赛的参与者。每个比赛会包含多个比赛，每个比赛包含多个参赛者。在这种情况下，我使用参与者唯一标识符HorsePK来识别我需要返回的记录。然后我试图实现的是确定他们在下一场比赛和之后的比赛中的结果，但不是可能发生的anu后续比赛。也会有参与者只完成了一场比赛或两场比赛的情况，因此这些记录应该被排除在外。

初始选择语句为：

Select RaceDate, HorsePK, HorseName, CareerRuns, FinishResult As ResultA
FROM [Punting].[dbo].[tblRace] 
Inner Join tblRaceEntrant On tblRace.RacePK = tblRaceEntrant.RaceFK
Inner Join tblRaceMeeting On tblRace.RaceMeetingFK = tblRaceMeeting.RaceMeetingPK
Inner Join tblHorse On tblRaceEntrant.HorseFK = tblHorse.HorsePK
Where CareerRuns < 1 And FinishResult = 1

这很好，并返回我期望的记录。当我试图对第二条语句执行内部联接时，我的问题就出现了，我认为问题是因为我只想按日期顺序为该参与者创建下一条记录。我认为语法应该类似于：

Select RaceDate, HorsePK, HorseName, CareerRuns, FinishResult As ResultA, ResultB
FROM [Punting].[dbo].[tblRace] t
Inner Join tblRaceEntrant On t.RacePK = tblRaceEntrant.RaceFK
Inner Join tblRaceMeeting On t.RaceMeetingFK = tblRaceMeeting.RaceMeetingPK
Inner Join tblHorse On tblRaceEntrant.HorseFK = tblHorse.HorsePK
Inner Join (Select Top (1) FinishResult As ResultB
FROM [Punting].[dbo].[tblRace] t1
Inner Join tblRaceEntrant On t1.RacePK = tblRaceEntrant.RaceFK
Inner Join tblRaceMeeting On t1.RaceMeetingFK = tblRaceMeeting.RaceMeetingPK
Inner Join tblHorse On tblRaceEntrant.HorseFK = tblHorse.HorsePK 
Order By RaceDate Desc) On t.HorsePK = t1.HorsePK

Where CareerRuns < 1 And FinishResult = 1

这将返回错误"关键字"On"附近的语法不正确"并引用了"Order By RaceDate Desc(On t.HorsePK=t1.HorsePK"。我真的不知道为什么这是错误的，也不知道它是否会返回我期望的记录，这就是我需要帮助的地方。

为了复合这一点，我认为我需要向嵌套的内部联接添加另一个内部联接，以获得该参与者的第三行。我试图实现的最终输出看起来像：

RaceDate,HorsePK,HorseName,CareerRuns,ResultA,ResultB,ResultC 
2017-03-12,52352,Just Hifalutin,0,1,4,2
2018-01-02,52837,Daunting Duchess,0,1,1,8
2017-07-16,53319,Yurilla,0,1,9,3
2017-04-25,53427,Maria Elisa,0,1,4,1

我对SQL server不是特别精通(显然(，但我相信这是一种比使用游标(我知道但不精通(更好的返回这些结果的方法。我还有别的办法吗？

与粘贴所涉及的每个表的DDL(因为它是相当多的数据(不同，这些是使用马ID的表的编辑版本：

CREATE TABLE [dbo].[tblHorse](
[HorsePK] [int] IDENTITY(1,1) NOT NULL,
[HorseName] [nvarchar](250) NOT NULL,
[SireFK] [int] NULL,
[DamFK] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblRaceEntrant](
[RaceEntrantPK] [int] IDENTITY(1,1) NOT NULL,
[RaceFK] [int] NOT NULL,
[EntrantNumber] [int] NOT NULL,
[HorseFK] [int] NOT NULL,
[Age] [int] NULL,
[Gender] [varchar](5) NULL,
[FinishResult] [int] NULL
) ON [PRIMARY]

tblHorse的示例数据如下：

HorsePK HorseName   SireFK  DamFK
77447   Scorpius    739 15383
84803   One Job 777 633
84815   Hooroo Lads 132 9797
84841   Ingear  766 21204
84884   Platinum Euros  143 11723
84889   Cryptic Wonder  211 17758
84912   Tricky Gal  90  19918
108448  Grey Detective  278 17515
109291  Nostradam Man   1347    6955
109319  Piccadilly Waltz    9   29848

tblRaceEntrant的示例代码如下：

RaceEntrantPK   RaceFK  EntrantNumber   HorseFK Age Gender  FinishResult
67  10635   14  77447   2   C   1
355 27  8   84803   2   F   1
694 66  8   84841   2   F   1
845 82  8   84815   2   G   1
1342    129 3   84884   2   G   1
1841    179 8   84889   2   G   1
2082    203 6   84912   3   M   1
5163    510 7   84841   2   F   3
5236    517 2   84815   2   G   10
8574    871 3   84889   2   G   2
10062   1026    1   84912   3   M   1
27640   2824    3   84912   4   M   2
36037   3673    5   84912   4   M   1
42829   4401    7   84912   4   M   1
49460   5110    1   84912   4   M   2
60466   6290    8   84841   3   F   14
62017   6453    11  77447   3   C   2
67086   6975    18  84841   3   F   8
71604   7450    10  77447   3   C   1
76138   7921    7   84841   3   F   5
77838   8102    1   77447   3   C   4
84153   8761    4   84841   3   F   2
85015   8843    4   77447   3   C   5
114210  11756   6   77447   2   C   7
505957  52335   10  84841   3   F   3
508693  52635   3   84889   3   G   1
511758  52943   15  84841   3   F   1
516101  53391   8   84884   3   G   1
518339  53625   1   84889   3   G   2
521441  53947   12  84841   3   F   3
537301  55557   4   84912   4   M   1
545700  56358   2   84912   4   M   6
561458  59583   11  84912   4   M   3
569822  60358   2   77447   3   G   2
572503  60587   4   84912   4   M   2
575946  60853   9   77447   3   G   8
580109  61296   9   77447   3   G   3
580253  61310   9   84912   4   M   7
587297  61953   6   77447   3   G   5
603412  63355   3   77447   4   G   10
608617  63805   9   84841   4   M   2
615265  64429   4   77447   4   G   2
618260  64741   3   77447   4   G   5
623080  65235   3   84912   5   M   1
625750  65507   3   108448  3   C   1
630404  65993   3   84912   5   M   5
635248  66478   4   109291  3   G   1
635604  66517   7   109319  3   0   1
635652  66522   7   108448  3   C   9
637189  66679   10  84912   5   M   5
638846  66857   5   109291  3   G   10

我尝试将外部选择修改为select t.HorsePK，而不是建议的select HorsePK。但这并没有解决错误。

Answer 1

您需要给子查询取别名(在我的示例中为"tx"(，然后使用此别名加入。此外，由于您想在t1.HorsePK上加入，您需要在子查询中选择它。

Select RaceDate, HorsePK, HorseName, CareerRuns, FinishResult As ResultA, ResultB
FROM [Punting].[dbo].[tblRace] t
Inner Join tblRaceEntrant On t.RacePK = tblRaceEntrant.RaceFK
Inner Join tblRaceMeeting On t.RaceMeetingFK = tblRaceMeeting.RaceMeetingPK
Inner Join tblHorse On tblRaceEntrant.HorseFK = tblHorse.HorsePK
Inner Join (Select Top (1) FinishResult As ResultB, t1.HorsePK /* <-- select anything you need outside of this subquery */
FROM [Punting].[dbo].[tblRace] t1
Inner Join tblRaceEntrant On t1.RacePK = tblRaceEntrant.RaceFK
Inner Join tblRaceMeeting On t1.RaceMeetingFK = tblRaceMeeting.RaceMeetingPK
Inner Join tblHorse On tblRaceEntrant.HorseFK = tblHorse.HorsePK 
Order By RaceDate Desc) tx On t.HorsePK = tx.HorsePK /* <-- Give an alias to you subquery and join on this alias*/

Where CareerRuns < 1 And FinishResult = 1