在SELECT查询中添加一列，但不参与分组

编程语言 2026-07-08

我有以下查询：

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
FROM
    tblPtsPerCompHistory as H
LEFT JOIN 
    tblCompetitors F ON F.Competitor_Idx = H.PtsFemale
LEFT JOIN 
    tblCompetitors M ON M.Competitor_Idx = H.PtsMale
LEFT OUTER JOIN 
    tblCouples C ON C.CoupleID = H.PtsCplID
JOIN 
    tblCompetitions CP ON CP.Competition_Idx = H.PtsCompID
WHERE 
    F.Surname + ' ' + F.First_Name IS NOT NULL
GROUP BY 
    H.PtsFemale, H.PtsMale
ORDER BY 
    1, 3 DESC

我需要把下面这个查询的结果作为结果集中的一列添加进去：

(SELECT CoupleNumber FROM tblCouples WHERE CoupleID = H.PtsCplID Order by Yearcpl DESC) As CoupleNumber

每年，合伙关系中的Couplenumber都会发生变化，因此需要获取最新的值。

我在“未包含在分组中”这个条件上遇到了问题，如果把它加入到最外层的SELECT语句，就会得到“ORDER BY不能内联使用”的错误。据我所读，我想像像CTE这样的结构，或者也许CROSS APPLY也有可能实现这一点，但我不确定。

解决方案

选项1

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, MAX(CPL.CoupleNumber) AS CoupleNumber, 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

    From tblPtsPerCompHistory as H
    LEFT JOIN tblCompetitors F on F.Competitor_Idx = H.PtsFemale
    LEFT JOIN tblCompetitors M on M.Competitor_Idx = H.PtsMale
    LEFT OUTER JOIN tblCouples C ON C.CoupleID = H.PtsCplID
    OUTER APPLY (
        SELECT TOP 1 CoupleNumber
        FROM tblCouples
        WHERE CoupleID = H.PtsCplID
        ORDER BY Yearcpl DESC
    ) AS CPL
    JOIN tblCompetitions CP ON CP.Competition_Idx = H.PtsCompID
    WHERE F.Surname +' '+ F.First_Name is not null
    GROUP BY H.PtsFemale, H.PtsMale
    ORDER BY 1, 3 DESC

更简单的做法是：

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, MAX(CPL.CoupleNumber) AS CoupleNumber, 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

    From tblPtsPerCompHistory as H
    LEFT JOIN tblCompetitors F on F.Competitor_Idx = H.PtsFemale
    LEFT JOIN tblCompetitors M on M.Competitor_Idx = H.PtsMale
    OUTER APPLY (
        SELECT TOP 1 CoupleNumber
        FROM tblCouples
        WHERE CoupleID = H.PtsCplID
        ORDER BY Yearcpl DESC
    ) AS CPL
    JOIN tblCompetitions CP ON CP.Competition_Idx = H.PtsCompID
    WHERE F.Surname +' '+ F.First_Name is not null
    GROUP BY H.PtsFemale, H.PtsMale
    ORDER BY 1, 3 DESC

SQL