我来帮你搞定这个需求。你现在的情况是已经对两个不同产品类型的数据集分别做了GROUP BY，再用UNION ALL合并，但想要对合并后的整体结果再统一按Client分组——核心思路就是把整个UNION ALL的结果包装成一个子查询或者CTE（公用表表达式），然后在外层执行GROUP BY操作。

具体实现方案 #

这里用CTE来封装合并后的数据集，这样代码可读性更高，也方便后续维护。同时我帮你修正了原代码里的语法问题（MAX(Test)Passeddate的写法有误），还去掉了多余的DISTINCT（因为GROUP BY已经保证每个Client只有一条记录，DISTINCT完全没必要）：

WITH CombinedData AS (
    -- 第一个产品类型（PC）的分组查询
    SELECT 
        MAX(Incep) AS Startdate,
        q1.Client,
        MAX(q1.Ref) AS Reference,
        MAX(Title) AS Title,
        MAX(Forename) AS Firstname,
        MAX(Surname) AS Lastname,
        MAX(Addr1) AS Line1,
        MAX(Addr2) AS line2,
        MAX(Addr3) AS line3,
        MAX(addr4) AS Line4,
        MAX(a1.Postcode) AS Postcode,
        MAX(Abode) AS Abodetype,
        MAX(Phone) AS Phone,
        MAX(fax) AS Faxnumber,
        MAX(Field1) AS Marketing,
        MAX(a1.Age) AS Ageofclient,
        MAX(TestPasseddate) AS Passeddate, -- 修正语法：假设字段名为TestPasseddate，若为Test字段则改为MAX(Test) AS Passeddate
        MAX(Resdate) AS residencydate,
        MAX(Occup) AS Occupation,
        MAX(EOccup) AS industry,
        MAX(RESTR) AS DriverRestrictions,
        MAX(Cover) AS Covertype,
        MAX(BONUS) AS NCBYears,
        MAX([USE]) AS Classofuse,
        MAX(REG) AS VehicleReg,
        MAX(ABI) AS VehicleABI,
        MAX(BOUGHT) AS PurchasedDate,
        MAX(Seats) AS VehicleSeats,
        MAX(a2.YEAR) AS Yearofmake,
        MAX(garage) AS Overnightlocation
    FROM Quotes q1
    LEFT JOIN agents QZ ON QZ.CDLCode = q1.Op COLLATE SQL_Latin1_General_CP1_CI_AS
    LEFT JOIN MIS_O_C a1 ON a1.Client = q1.client
    LEFT JOIN MIS_O_PPC a2 ON a2.Client = q1.client AND a2.Ref = Q1.Ref
    WHERE 
        EDate > GETDATE()-180 
        AND reportgroup NOT IN ('Renewals', 'Underwriting', 'Exclude') 
        AND Occup NOT IN ('Delivery Courier','Courier') 
        AND Field1 = 'CROSS' 
        AND SEATS > '8' 
        AND [USE] NOT IN ('3') 
        AND Source IN ('DIREC', '1A984', '1A997','1A982', '1A998') 
        AND Ptype = 'PC'
    GROUP BY q1.Client

    UNION ALL

    -- 第二个产品类型（GV）的分组查询
    SELECT 
        MAX(Incep) AS Startdate,
        q1.Client,
        MAX(q1.Ref) AS Reference,
        MAX(Title) AS Title,
        MAX(Forename) AS Firstname,
        MAX(Surname) AS Lastname,
        MAX(Addr1) AS Line1,
        MAX(Addr2) AS line2,
        MAX(Addr3) AS line3,
        MAX(addr4) AS Line4,
        MAX(a1.Postcode) AS Postcode,
        MAX(Abode) AS Abodetype,
        MAX(Phone) AS Phone,
        MAX(fax) AS Faxnumber,
        MAX(Field1) AS Marketing,
        MAX(a1.Age) AS Ageofclient,
        MAX(TestPasseddate) AS Passeddate, -- 同样修正语法
        MAX(Resdate) AS residencydate,
        MAX(Occup) AS Occupation,
        MAX(EOccup) AS industry,
        MAX(RESTR) AS DriverRestrictions,
        MAX(Cover) AS Covertype,
        MAX(BONUS) AS NCBYears,
        MAX([USE]) AS Classofuse,
        MAX(REG) AS VehicleReg,
        MAX(ABI) AS VehicleABI,
        MAX(BOUGHT) AS PurchasedDate,
        MAX(Seats) AS VehicleSeats,
        MAX(a2.YEAR) AS Yearofmake,
        MAX(garage) AS Overnightlocation
    FROM Quotes q1
    LEFT JOIN agents QZ ON QZ.CDLCode = q1.Op COLLATE SQL_Latin1_General_CP1_CI_AS
    LEFT JOIN MIS_O_C a1 ON a1.Client = q1.client
    LEFT JOIN MIS_O_PGV a2 ON a2.Client = q1.client AND a2.Ref = Q1.Ref
    WHERE 
        EDate > GETDATE()-180 
        AND reportgroup NOT IN ('Renewals', 'Underwriting', 'Exclude') 
        AND Occup NOT IN ('Delivery Courier','Courier') 
        AND Field1 = 'CROSS' 
        AND SEATS > '8' 
        AND [USE] NOT IN ('3') 
        AND Source IN ('DIREC', '1A984', '1A997','1A982', '1A998') 
        AND Ptype = 'GV'
    GROUP BY q1.Client
)
-- 外层对合并后的所有数据按Client统一分组
SELECT 
    MAX(Startdate) AS Startdate,
    Client,
    MAX(Reference) AS Reference,
    MAX(Title) AS Title,
    MAX(Firstname) AS Firstname,
    MAX(Lastname) AS Lastname,
    MAX(Line1) AS Line1,
    MAX(line2) AS line2,
    MAX(line3) AS line3,
    MAX(Line4) AS Line4,
    MAX(Postcode) AS Postcode,
    MAX(Abodetype) AS Abodetype,
    MAX(Phone) AS Phone,
    MAX(Faxnumber) AS Faxnumber,
    MAX(Marketing) AS Marketing,
    MAX(Ageofclient) AS Ageofclient,
    MAX(Passeddate) AS Passeddate,
    MAX(residencydate) AS residencydate,
    MAX(Occupation) AS Occupation,
    MAX(industry) AS industry,
    MAX(DriverRestrictions) AS DriverRestrictions,
    MAX(Covertype) AS Covertype,
    MAX(NCBYears) AS NCBYears,
    MAX(Classofuse) AS Classofuse,
    MAX(VehicleReg) AS VehicleReg,
    MAX(VehicleABI) AS VehicleABI,
    MAX(PurchasedDate) AS PurchasedDate,
    MAX(VehicleSeats) AS VehicleSeats,
    MAX(Yearofmake) AS Yearofmake,
    MAX(Overnightlocation) AS Overnightlocation
FROM CombinedData
GROUP BY Client;

关键说明 #

1. 语法修正：原代码中MAX(Test)Passeddate是错误写法，我假设字段名为TestPasseddate，如果实际是要取Test字段的最大值并命名为Passeddate，请修改为MAX(Test) AS Passeddate。
2. 去掉DISTINCT：子查询里的DISTINCT完全多余，因为GROUP BY q1.Client已经确保每个客户只返回一条记录，保留它只会增加不必要的性能开销。
3. 外层分组逻辑：外层同样按Client分组，并用MAX聚合所有非分组字段，和子查询的逻辑保持一致，确保同一个客户的两条记录（如果同时存在于PC和GV两个数据集）被合并成一条。

内容的提问来源于stack exchange，提问作者Jamie Martin