SQL实现：为每个catID生成排除自身的同窝猫咪信息字符串

测试数据 #

DROP TABLE IF EXISTS cats;

CREATE TABLE cats
(
    litterID int NOT NULL,
    catID int NOT null,  -- 行唯一标识
    catFirst varchar(30),
    catLast varchar(30),
    catSize int
);

INSERT INTO cats (litterID,catID,catFirst,catLast,catSize)
VALUES
('20020314','20262607','Scat','Meow','8'),
('20020314','20264036','Paws','Furbs','4'),
('20020314','20264267','Alexander','Meow','2');

需求说明 #

需要为每只猫的catID生成其同窝猫咪的信息字符串，核心要求是排除当前猫本身，预期输出格式如下：

catID     catFirst     litterID  littermates
--------  ---------    --------  -----------------------------------------------
20262607  Scat         20020314  "Paws Furbs Size 4; Alexander Meow Size 2"
20264036  Paws         20020314  "Scat Meow Size 8; Alexander Meow Size 2"
20264267  Alexander    20020314  "Scat Meow Size 8; Paws Furbs Size 4"

最佳实现方案（CROSS APPLY + STRING_AGG） #

使用CROSS APPLY关联同窝的其他猫咪，再通过STRING_AGG函数直接拼接所需字符串，这种方法避免了游标、循环的低效，也比EXCEPT更简洁高效：

SELECT 
    c.catID,
    c.catFirst,
    c.litterID,
    CONCAT('"', STRING_AGG(CONCAT(cm.catFirst, ' ', cm.catLast, ' Size ', cm.catSize), '; '), '"') AS littermates
FROM cats c
CROSS APPLY (
    SELECT catFirst, catLast, catSize 
    FROM cats 
    WHERE litterID = c.litterID 
      AND catID != c.catID
) cm
GROUP BY c.catID, c.catFirst, c.litterID
ORDER BY c.catID;

思路说明

1. CROSS APPLY子查询：针对每一行主表的猫，筛选出同litterID但catID不同的同窝猫咪；
2. STRING_AGG：将同窝猫咪的信息按"名 姓 Size 体型"格式拼接，用; 分隔；
3. CONCAT给拼接结果加上前后双引号，匹配预期输出格式；
4. 最后按主表的catID分组，确保每只猫对应一条结果。

输出验证 #

执行上述SQL后，输出结果完全符合预期格式，高效且易维护。

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