如何在SQL Server中按面积排序填充一对多关联的多County字段

核心思路 #

先对ZIP_County_Intersect表中每个ZIP码对应的县按SqMiles_ZIPparts降序排名，再通过行转列（PIVOT）将最多5条记录转为多列格式，最后关联ZIP_County_Dissolve表完成字段填充。

具体实现代码 #

-- 生成带排序序号的中间数据集
WITH RankedCounties AS (
    SELECT
        ZIP,
        CountyName,
        SqMiles_ZIPparts,
        -- 按ZIP分组，面积降序排名，仅保留前5个关联县
        ROW_NUMBER() OVER (PARTITION BY ZIP ORDER BY SqMiles_ZIPparts DESC) AS CountyRank
    FROM ZIP_County_Intersect
),
-- 行转列处理县名字段
PivotedCounties AS (
    SELECT
        ZIP,
        [1] AS County1,
        [2] AS County2,
        [3] AS County3,
        [4] AS County4,
        [5] AS County5
    FROM RankedCounties
    PIVOT (
        MAX(CountyName)
        FOR CountyRank IN ([1], [2], [3], [4], [5])
    ) AS PivotCounties
),
-- 行转列处理面积字段
PivotedAreas AS (
    SELECT
        ZIP,
        [1] AS County1_SqMiles,
        [2] AS County2_SqMiles,
        [3] AS County3_SqMiles,
        [4] AS County4_SqMiles,
        [5] AS County5_SqMiles
    FROM RankedCounties
    PIVOT (
        MAX(SqMiles_ZIPparts)
        FOR CountyRank IN ([1], [2], [3], [4], [5])
    ) AS PivotAreas
)
-- 关联目标表完成字段更新
UPDATE d
SET
    d.County1 = pc.County1,
    d.County1_SqMiles = pa.County1_SqMiles,
    d.County2 = pc.County2,
    d.County2_SqMiles = pa.County2_SqMiles,
    d.County3 = pc.County3,
    d.County3_SqMiles = pa.County3_SqMiles,
    d.County4 = pc.County4,
    d.County4_SqMiles = pa.County4_SqMiles,
    d.County5 = pc.County5,
    d.County5_SqMiles = pa.County5_SqMiles
FROM ZIP_County_Dissolve d
LEFT JOIN PivotedCounties pc ON d.ZIP = pc.ZIP
LEFT JOIN PivotedAreas pa ON d.ZIP = pa.ZIP;

关键说明 #

• ROW_NUMBER()：给每个ZIP下的县按面积从大到小生成1-5的排名，自动过滤超出5条的关联记录。
• PIVOT：分别对县名和面积做行转列，把同一ZIP下的纵向关联数据转为横向多列格式。
• LEFT JOIN：确保即使ZIP没有匹配到县数据，目标表对应字段也会被设为NULL，适配空字段填充需求。

内容的提问来源于stack exchange，提问作者rachel.passer