GROUP_CONCAT如何排除当前值及通过父唯一ID查询同级记录名称SQL问题

问题说明 #

现有写法的核心问题是仅按父级唯一IDSubApplicationId分组后，只会返回每个分组下的首行申请人信息，既无法遍历展示所有申请人的单独条目，也无法在GROUP_CONCAT中排除当前行对应的数值。

方案1：自关联实现（全SQL版本兼容） #

通过表自关联匹配同组其他申请人，关联时直接排除当前行，适配MySQL 5.x等低版本数据库：

SELECT 
    t1.`SubAppId` AS `AppId`,
    t1.`SubApplicationId` AS `ApplicationId`,
    t1.`SubFirstName`,
    t1.`SubSurname`,
    IFNULL(
        GROUP_CONCAT(CONCAT(t2.`SubFirstName`, ' ', t2.`SubSurname`) SEPARATOR ', '),
        ''
    ) AS `SubCoApplicantsNames`
FROM `SubApplicants` t1
LEFT JOIN `SubApplicants` t2 
    ON t1.`SubApplicationId` = t2.`SubApplicationId` 
    AND t1.`SubAppId` != t2.`SubAppId` -- 排除当前行
    AND t2.`IsSubGuarantor` = 0
WHERE t1.`IsSubGuarantor` = 0
GROUP BY t1.`SubAppId`, t1.`SubApplicationId`, t1.`SubFirstName`, t1.`SubSurname`

如果当前申请人无同组其他兄弟记录，SubCoApplicantsNames会返回空字符串，可根据需求调整IFNULL的默认值。

方案2：窗口函数实现（适配MySQL 8.0+/ PostgreSQL等新版数据库） #

用窗口函数按父级ID分组后直接排除当前行，无需自关联，性能更优：

SELECT 
    `SubAppId` AS `AppId`,
    `SubApplicationId` AS `ApplicationId`,
    `SubFirstName`,
    `SubSurname`,
    GROUP_CONCAT(
        CONCAT(`SubFirstName`, ' ', `SubSurname`)
        SEPARATOR ', '
    ) OVER (
        PARTITION BY `SubApplicationId`
        EXCLUDE CURRENT ROW
    ) AS `SubCoApplicantsNames`
FROM `SubApplicants`
WHERE `IsSubGuarantor` = 0

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