SQL分组与嵌套拼接：如何实现目标聚合输出？

安全阿岳

2026-6-21

问题描述

原始样本数据：

ID	Continent	Country	City
1	Africa	Egypt	Cairo
2	Africa	Egypt	Alexandria
3	Africa	Egypt	Luxor
4	Africa	Morocco	Rabat
5	Africa	Morocco	Casablanca
6	Asia	China	Beijing
7	Asia	China	Shanghai

期望输出：

ID	Continent	CountryAndCities
1	Africa	Egypt(Cairo,Alexandria,Luxor) - Morocco(Rabat,Casablanca)
2	Asia	China(Beijing,Shanghai)

已尝试代码：

SELECT DISTINCT
    Country,
    ISNULL(STUFF((SELECT CONCAT('- ', City)
                  FROM Continental s2
                  WHERE s2.Country = s.Country
                  GROUP BY City
                  FOR XML PATH ('')), 1, 1, ''), '') AS Cities
FROM
    Continental s
GROUP BY
    Country

当前输出：

Country	Cities
Egypt	Cairo- Alexandria- Luxor
Morocco	Casablanca- Rabat
China	Beijing- Shanghai

需要修改代码以得到期望的目标输出。

修改后的SQL代码

WITH CountryCities AS (
    -- 按国家聚合城市，生成「国家(城市1,城市2...)」格式字符串
    SELECT 
        Continent,
        Country,
        CONCAT(Country, '(', 
               STUFF((SELECT CONCAT(',', City)
                      FROM Continental s2
                      WHERE s2.Country = s.Country
                      ORDER BY City
                      FOR XML PATH('')), 1, 1, ''), 
               ')') AS CountryCityStr
    FROM Continental s
    GROUP BY Continent, Country
),
ContinentGroups AS (
    -- 按大洲聚合国家城市字符串，生成最终组合字段
    SELECT 
        Continent,
        STUFF((SELECT CONCAT(' - ', CountryCityStr)
               FROM CountryCities cc2
               WHERE cc2.Continent = cc1.Continent
               ORDER BY Country
               FOR XML PATH('')), 1, 3, '') AS CountryAndCities
    FROM CountryCities cc1
    GROUP BY Continent
)
-- 生成带自增ID的最终结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY Continent) AS ID,
    Continent,
    CountryAndCities
FROM ContinentGroups

代码说明

CountryCities 公共表表达式：
- 按大洲和国家分组，对每个国家下的城市进行逗号拼接，再用国家名和括号包裹，生成类似Egypt(Cairo,Alexandria,Luxor)的字符串。
- 用STUFF移除拼接后开头多余的逗号。
ContinentGroups 公共表表达式：
- 按大洲分组，将同一大洲的国家城市字符串用-拼接，生成目标格式的CountryAndCities字段。
- 用STUFF移除开头多余的-（长度为3，包含空格和破折号）。
最终查询：
- 用ROW_NUMBER()生成每个大洲对应的ID，按大洲排序输出。

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