在联合查询中使用顶部访问 2016

Question

我正在尝试创建一个查询，该查询将根据其他两个查询的并集输出前 6 个结果。这两个查询如下所示：

SELECT GameData.GameID,
GameData.DivisionID,
GameData.SeasonID,
GameData.HomeTeamID AS TeamID,
GameData.GameDate,
IIf([FullTimeResult] = 'H', 1, 0) AS W,
IIf([FullTimeResult] = 'D', 1, 0) AS D,
IIf([FullTimeResult] = 'A', 1, 0) AS L,
IIf([HalfTimeResult] = 'H', 1, 0) AS WHT,
IIf([HalfTimeResult] = 'D', 1, 0) AS DHT,
IIf([HalfTimeResult] = 'A', 1, 0) AS LHT,
GameData.FullTimeHomeGoals AS GS,
GameData.FullTimeAwayGoals AS GC,
IIf([FullTimeResult] = 'H', 3, IIf([FullTimeResult] = 'D', 1, 0)) AS P,
GameData.HalfTimeHomeGoals AS GSHT,
GameData.HalfTimeAwayGoals AS GCHT,
GameData.HomeShots AS Sh,
GameData.AwayShots AS ShA,
GameData.HomeShotsOnTarget AS ShOnT,
GameData.AwayShotsOnTarget AS ShAOnT,
GameData.HomeFouls AS FM,
GameData.AwayFouls AS FA,
GameData.HomeCorners AS C,
GameData.AwayCorners AS CA,
GameData.HomeYellowCards AS YC,
GameData.AwayYellowCards AS YCA,
GameData.HomeRedCards AS RC,
GameData.AwayRedCards AS RCA
FROM TeamsDivSea
INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.HomeTeamID
WHERE (
(
(GameData.GameID) IN (
SELECT TOP 3 GameID
FROM GameData AS Dupe
WHERE Dupe.HomeTeamID = GameData.HomeTeamID
ORDER BY Dupe.GameDate DESC,
Dupe.GameID DESC
)
)
);
SELECT GameData.GameID,
GameData.DivisionID,
GameData.SeasonID,
GameData.AwayTeamID AS TeamID,
GameData.GameDate,
IIf([FullTimeResult] = 'A', 1, 0) AS W,
IIf([FullTimeResult] = 'D', 1, 0) AS D,
IIf([FullTimeResult] = 'H', 1, 0) AS L,
IIf([HalfTimeResult] = 'A', 1, 0) AS WHT,
IIf([HalfTimeResult] = 'D', 1, 0) AS DHT,
IIf([HalfTimeResult] = 'H', 1, 0) AS LHT,
GameData.FullTimeAwayGoals AS GS,
GameData.FullTimeHomeGoals AS GC,
IIf([FullTimeResult] = 'A', 3, IIf([FullTimeResult] = 'D', 1, 0)) AS P,
GameData.HalfTimeAwayGoals AS GSHT,
GameData.HalfTimeHomeGoals AS GCHT,
GameData.AwayShots AS Sh,
GameData.HomeShots AS ShA,
GameData.AwayShotsOnTarget AS ShOnT,
GameData.HomeShotsOnTarget AS ShAOnT,
GameData.AwayFouls AS FM,
GameData.HomeFouls AS FA,
GameData.AwayCorners AS C,
GameData.HomeCorners AS CA,
GameData.AwayYellowCards AS YC,
GameData.HomeYellowCards AS YCA,
GameData.AwayRedCards AS RC,
GameData.HomeRedCards AS RCA
FROM TeamsDivSea
INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.AwayTeamID
WHERE (
(
(GameData.GameID) IN (
SELECT TOP 3 GameID
FROM GameData AS Dupe
WHERE Dupe.AwayTeamID = GameData.AwayTeamID
ORDER BY Dupe.GameDate DESC,
Dupe.GameID DESC
)
)
);

是否可以仅使用一个 SELECT TOP 语句创建联合查询，以便仅从联接结果中输出前 6 个？

非常感谢

Answer 1

这应该有效 - 基本上将你的两个查询统一起来，然后选择前 6 个。

SELECT TOP 6 GameID, DivisionID, SeasonID, TeamID, GameDate, W,D,L,WHT,DHT,LHT,GS,GC,P,GSHT,GCHT,Sh,shA, shOnT, ShAOnt, FM,FA,C,CA,YC,YCA,RC,RCA
FROM    (
SELECT GameData.GameID,
GameData.DivisionID,
GameData.SeasonID,
GameData.HomeTeamID AS TeamID,
GameData.GameDate,
IIf([FullTimeResult] = 'H', 1, 0) AS W,
IIf([FullTimeResult] = 'D', 1, 0) AS D,
IIf([FullTimeResult] = 'A', 1, 0) AS L,
IIf([HalfTimeResult] = 'H', 1, 0) AS WHT,
IIf([HalfTimeResult] = 'D', 1, 0) AS DHT,
IIf([HalfTimeResult] = 'A', 1, 0) AS LHT,
GameData.FullTimeHomeGoals AS GS,
GameData.FullTimeAwayGoals AS GC,
IIf([FullTimeResult] = 'H', 3, IIf([FullTimeResult] = 'D', 1, 0)) AS P,
GameData.HalfTimeHomeGoals AS GSHT,
GameData.HalfTimeAwayGoals AS GCHT,
GameData.HomeShots AS Sh,
GameData.AwayShots AS ShA,
GameData.HomeShotsOnTarget AS ShOnT,
GameData.AwayShotsOnTarget AS ShAOnT,
GameData.HomeFouls AS FM,
GameData.AwayFouls AS FA,
GameData.HomeCorners AS C,
GameData.AwayCorners AS CA,
GameData.HomeYellowCards AS YC,
GameData.AwayYellowCards AS YCA,
GameData.HomeRedCards AS RC,
GameData.AwayRedCards AS RCA
FROM TeamsDivSea
INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.HomeTeamID
WHERE (
(
(GameData.GameID) IN (
SELECT TOP 3 GameID
FROM GameData AS Dupe
WHERE Dupe.HomeTeamID = GameData.HomeTeamID
ORDER BY Dupe.GameDate DESC,
Dupe.GameID DESC
)
)
)
UNION ALL SELECT GameData.GameID,
GameData.DivisionID,
GameData.SeasonID,
GameData.AwayTeamID AS TeamID,
GameData.GameDate,
IIf([FullTimeResult] = 'A', 1, 0),
IIf([FullTimeResult] = 'D', 1, 0),
IIf([FullTimeResult] = 'H', 1, 0),
IIf([HalfTimeResult] = 'A', 1, 0),
IIf([HalfTimeResult] = 'D', 1, 0),
IIf([HalfTimeResult] = 'H', 1, 0),
GameData.FullTimeAwayGoals,
GameData.FullTimeHomeGoals,
IIf([FullTimeResult] = 'A', 3, IIf([FullTimeResult] = 'D', 1, 0)),
GameData.HalfTimeAwayGoals,
GameData.HalfTimeHomeGoals,
GameData.AwayShots,
GameData.HomeShots,
GameData.AwayShotsOnTarget,
GameData.HomeShotsOnTarget,
GameData.AwayFouls,
GameData.HomeFouls,
GameData.AwayCorners,
GameData.HomeCorners,
GameData.AwayYellowCards,
GameData.HomeYellowCards,
GameData.AwayRedCards,
GameData.HomeRedCards
FROM TeamsDivSea
INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.AwayTeamID
WHERE (
(
(GameData.GameID) IN (
SELECT TOP 3 GameID
FROM GameData AS Dupe
WHERE Dupe.AwayTeamID = GameData.AwayTeamID
ORDER BY Dupe.GameDate DESC,
Dupe.GameID DESC
)
)
)
)