如何为分组SELECT查询新增最新CoupleNumber列并解决分组报错？

咱们用OUTER APPLY（SQL Server、Azure SQL等支持APPLY语法的数据库都能用）就能搞定这个需求——既拿到每个PtsCplID对应的最新CoupleNumber，又能避开分组错误和内联子查询不能用ORDER BY的坑，具体修改如下：

修改后的完整查询语句 #

SELECT 
    MAX(F.Surname + ',  ' + F.First_Name) AS Female, 
    ISNULL(MAX(M.Surname + ',  ' + M.First_Name),'') AS Male, 
    MAX(CP.Comp_Date) AS Comp_Date, 
    H.PtsFemale, H.PtsMale AS PtsMale, 
    ISNULL(MAX(F.eMail_Address), '') AS eMail, 
    MAX(F.Registered + 0) AS registered, 
    ISNULL(REPLACE(MAX(F.First_Name + '  ' + F.Surname), '.', ''), '') AS Fname, 
    ISNULL(REPLACE(MAX(M.First_Name + '  ' + M.Surname), '.', ''), '') AS Mname,
    ISNULL(LatestCouple.CoupleNumber, '') AS CoupleNumber -- 新增的最新CoupleNumber列
FROM
    tblPtsPerCompHistory as H
LEFT JOIN 
    tblCompetitors F ON F.Competitor_Idx = H.PtsFemale
LEFT JOIN 
    tblCompetitors M ON M.Competitor_Idx = H.PtsMale
-- 替换原来的tblCouples左连接，用OUTER APPLY精准取最新值
OUTER APPLY (
    SELECT TOP 1 CoupleNumber
    FROM tblCouples
    WHERE CoupleID = H.PtsCplID
    ORDER BY Yearcpl DESC -- 按年份倒序，取最新的那一条
) AS LatestCouple
JOIN 
    tblCompetitions CP ON CP.Competition_Idx = H.PtsCompID
WHERE 
    F.Surname + ' ' + F.First_Name IS NOT NULL
GROUP BY 
    H.PtsFemale, H.PtsMale, LatestCouple.CoupleNumber -- 要是同一分组里CoupleNumber唯一，加这里就行；不唯一就看下面说明
ORDER BY 
    1, 3 DESC

关键细节说明 #

1. 为什么用OUTER APPLY？
   它能针对tblPtsPerCompHistory的每一行，根据当前行的PtsCplID去tblCouples里捞数据，配合TOP 1和ORDER BY Yearcpl DESC直接拿到最新的CoupleNumber，完美解决内联子查询不能用ORDER BY的问题。而且用OUTER而不是CROSS，是为了兼容PtsCplID为空的情况，避免丢数据。

2. 分组的两种情况

   • 如果同一对搭档（PtsFemale+PtsMale）对应的CoupleNumber是唯一的（符合业务逻辑，毕竟要的是最新值），直接把LatestCouple.CoupleNumber加到GROUP BY里就行；
   • 要是同一分组里出现了不同的CoupleNumber（比如同一对搭档多次换号），就把SELECT里的CoupleNumber改成MAX(LatestCouple.CoupleNumber)，同时不用把它加到GROUP BY里。

3. NULL值处理
   用ISNULL(LatestCouple.CoupleNumber, '')确保当没有对应Couple记录时，这列显示空字符串，不会出现NULL。

内容的提问来源于stack exchange，提问作者Hugh Self Taught