SQL Server - 获取 Group By 查询中的特定数据

Question

我有一个 SQL 存储过程，用于汇总棒球队的结果。我对适当的列进行“分组”，并对所选查询的统计数据所需的其他列进行求和。它工作得很好并且给了我我想要的结果。

我遇到的情况是，如果一个玩家可以在同一赛季内属于不同的球队，我无法按球队分组。我所做的是使用子查询来获取玩家的当前球队。再次，工作正常。但我需要获得更多个人信息，例如球员当前的 teamID。我无法组合上述子查询中的两列。针对主查询运行多个子查询是一种不好的做法吗？使用分组依据时是否有其他方法可以从查询中获取特定（或不同）信息？

编辑 添加了表架构。

SELECT LS.SeasonName, A.SeasonID, PM.PlayerID, PM.PFirstName, PM.PLastName,
(SELECT     ILT.TeamName
                        FROM          League_T AS ILT INNER JOIN
                                               Season_T AS IST ON ILT.TeamID = IST.TeamID INNER JOIN
                                               Season_P AS ISP ON IST.SeasonTeamID = ISP.Season_TeamID
                        WHERE      (ISP.PlayerID = PM.PlayerID) AND (ISP.IsCurrent = 1)) AS 'CurrentTM', 
                       (SELECT     ILT.TeamID
                        FROM          League_Team AS ILT INNER JOIN
                                               Season_Team AS IST ON ILT.TeamID = IST.TeamID INNER JOIN
                                               Season_P AS ISP ON IST.SeasonTeamID = ISP.Season_TeamID
                        WHERE      (ISP.PlayerID = PM.PlayerID) AND (ISP.IsCurrent = 1)) AS 'CurrentTMID', 
Count(Case WHEN GS = 1 THEN GS END) AS GS, --multiple SUM columns here--
FROM Player_Master PM
INNER JOIN Season_P ON PM.PlayerID = Season_P.PlayerID 
INNER JOIN Season_S AS A 
INNER JOIN Season_SD AS B ON A.GameID = B.GameID
INNER JOIN Season_PP AS C ON B.OutcomeID = C.OutcomeID ON Season_P.Season_PlayerID = C.SeasonPlayerID
INNER JOIN League_Season AS LS ON A.SeasonID = LS.SeasonID
WHERE (B.TeamID = @TeamID) AND (LS.IsCurrent = 1)
Group By LS.SeasonName, A.SeasonID, PM.PlayerID, PM.PFirstName, PM.PLastName

Answer 1

如果您的查询需要相关子查询，那么这没有什么问题......假设您实际上需要这样做。

您不能JOIN到“players”表，然后基于“player.current_team”列加入到“teams”表吗？