基于规则合并Wedding事件住户姓名的SQL实现问询

处理规则 #

• 若EventType为Birthday，同一HouseID的住户各行独立显示；
• 若EventType为Wedding，仅当HouseID、EventType、EventDate均相同时，按规则合并同组两人姓名：
  • 姓氏相同：格式为“名1 and 名2 姓氏”（如Will and Mary Stanton）
  • 姓氏不同：格式为“名1 姓氏1 and 名2 姓氏2”（如Stephen Jacobs and Janetsy Lilly）
• 若同组仅1人，则单独成行。

示例输入表 #

DECLARE @t TABLE (
    HouseID INT,
    FirstName NVARCHAR(64),
    LastName NVARCHAR(64),
    EventType NVARCHAR(64),
    EventDate DATE
);

INSERT INTO @t (HouseID, FirstName, LastName, EventType, EventDate)
VALUES
    (1, 'Will', 'Stanton', 'Birthday', '1974-01-05'),
    (1, 'Mary', 'Stanton', 'Birthday', '1980-05-22'),
    (2, 'Jason', 'Stockmore', 'Birthday', '1987-12-07'),
    (3, 'Mark', 'Mellony', 'Wedding', '2021-04-04'),
    (3, 'Stacy', 'Mellony', 'Wedding', '2021-04-04'),
    (4, 'Stephen', 'Johnson', 'Wedding', '2012-01-30'),
    (4, 'Janetsy', 'Johnson', 'Wedding', '2012-01-30'),
    (5, 'George', 'Jackson', 'Wedding', '2009-11-15'),
    (5, 'Sally', 'Mistmoore', 'Wedding', '2009-11-15'),
    (6, 'Sandy', 'Katz', 'Wedding', '2010-03-19'),
    (6, 'Jeff', 'Trilov', 'Wedding', '2016-09-09'),
    (7, 'Sandra', 'Kirchbaum', 'Wedding', '2011-05-22'),
    (8, 'Jessica', 'Bower', 'Birthday', '1996-02-26'),
    (8, 'Frank', 'Fjorn', 'Birthday', '1969-07-19');

期望输出结果 #

用户尝试的方法（仅支持姓氏相同场景） #

SELECT HouseID,
       FirstName
     , LastName
     , EventType
     , EventDate
     , RowNum = ROW_NUMBER() OVER (PARTITION BY LastName, EventType ORDER BY 1/0)
     , Values1 = CAST(NULL AS VARCHAR(MAX))
INTO #EntityValues1
FROM @t
WHERE EventType = 'Wedding'

UPDATE #EntityValues1
SET @Values1 = Values1 =
        CASE WHEN RowNum = 1 
            THEN FirstName
            ELSE @Values1 + ' and ' + FirstName 
        END

解决方案：使用STUFF+FOR XML实现姓名合并 #

以下SQL可以同时处理姓氏相同/不同的Wedding场景，且保留Birthday的独立行：

WITH WeddingGroups AS (
    SELECT 
        HouseID,
        EventType,
        EventDate,
        LastName,
        FirstName,
        -- 统计同组内不同姓氏的数量
        COUNT(DISTINCT LastName) OVER (PARTITION BY HouseID, EventType, EventDate) AS DistinctLastNamesCount,
        -- 拼接同组内的姓名片段
        STUFF((
            SELECT ' and ' + CASE 
                                WHEN COUNT(DISTINCT LastName) OVER (PARTITION BY HouseID, EventType, EventDate) = 1 
                                THEN FirstName 
                                ELSE FirstName + ' ' + LastName 
                            END
            FROM @t t2
            WHERE t2.HouseID = t1.HouseID 
              AND t2.EventType = t1.EventType 
              AND t2.EventDate = t1.EventDate
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 5, '') AS MergedNames,
        -- 同组统一姓氏（仅当全部相同时有效）
        MAX(LastName) OVER (PARTITION BY HouseID, EventType, EventDate) AS GroupLastName
    FROM @t t1
    WHERE EventType = 'Wedding'
)
-- 合并Birthday和处理后的Wedding数据
SELECT 
    HouseID,
    CASE 
        WHEN EventType = 'Birthday' THEN FirstName + ' ' + LastName
        ELSE CASE 
                WHEN DistinctLastNamesCount = 1 THEN MergedNames + ' ' + GroupLastName
                ELSE MergedNames
             END
    END AS FinalName,
    EventType,
    EventDate
FROM (
    -- 保留所有Birthday行
    SELECT HouseID, FirstName, LastName, EventType, EventDate, NULL AS DistinctLastNamesCount, NULL AS MergedNames, NULL AS GroupLastName
    FROM @t
    WHERE EventType = 'Birthday'
    UNION ALL
    -- 去重Wedding组，仅保留每组一行
    SELECT DISTINCT HouseID, NULL, NULL, EventType, EventDate, DistinctLastNamesCount, MergedNames, GroupLastName
    FROM WeddingGroups
) Combined
ORDER BY HouseID, EventDate;

代码说明 #

1. WeddingGroups CTE：
   • 按HouseID, EventType, EventDate分组，判断组内姓氏是否统一；
   • 用STUFF+FOR XML拼接姓名：姓氏统一时只拼接名字，否则拼接完整“名+姓”；
2. 结果合并：
   • 直接生成Birthday类型的“名+姓”格式；
   • 根据姓氏是否统一，生成Wedding类型的两种合并格式；
3. 排序：按HouseID和EventDate排序，与示例输出一致。

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