按街道及连续区域分组求地址编号起止值的SQL问题

问题描述 #

需要创建地址目录，要求：

• 按ADRESS_KEY（街道键）分组
• 每条记录包含ADRESS_NO_START（起始地址编号）和ADRESS_NO_END（结束地址编号）
• 当REGION1-REGION4区域变量发生变化时，拆分新的记录

使用基础GROUP BY/MIN-MAX方法会错误合并非连续的同区域记录：原本应分为3组（1-5、7-9、11-13），但结果合并了前后两次REGION1=003的记录，得到2条错误结果（1-13、7-9）。

解决方案 #

使用窗口函数识别连续的同区域分组，核心思路是通过对比当前行与上一行的区域组合，生成唯一的分组标识，再基于该标识聚合计算起止地址。

完整SQL代码 #

DECLARE @adressInRegion AS TABLE (
    ADRESS_KEY CHAR(5),
    ADRESS_NO INT,
    REGION1 CHAR(3),
    REGION2 CHAR(2),
    REGION3 CHAR(5),
    REGION4 CHAR(2)
)

INSERT INTO @adressInRegion 
VALUES
('12345',1,'003','02','55555','12'),
('12345',3,'003','02','55555','12'),
('12345',5,'003','02','55555','12'),
('12345',7,'005','02','55555','12'),
('12345',9,'005','02','55555','12'),
('12345',11,'003','02','55555','12'),
('12345',13,'003','02','55555','12')

-- 生成连续分组标识并聚合
WITH AddressGroups AS (
    SELECT 
        *,
        -- 区域组合变化时，分组ID递增
        SUM(CASE 
            WHEN CONCAT(REGION1, REGION2, REGION3, REGION4) = LAG(CONCAT(REGION1, REGION2, REGION3, REGION4)) OVER (PARTITION BY ADRESS_KEY ORDER BY ADRESS_NO)
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY ADRESS_KEY ORDER BY ADRESS_NO) AS GroupID
    FROM @adressInRegion
)
SELECT 
    ADRESS_KEY,
    MIN(ADRESS_NO) AS ADRESS_NO_START,
    MAX(ADRESS_NO) AS ADRESS_NO_END,
    REGION1,
    REGION2,
    REGION3,
    REGION4
FROM AddressGroups
GROUP BY 
    ADRESS_KEY,
    GroupID,
    REGION1,
    REGION2,
    REGION3,
    REGION4
ORDER BY ADRESS_NO_START;

代码说明 #

1. CTE（AddressGroups）：
   • 用LAG函数获取上一行的区域组合（通过CONCAT合并四个REGION字段）
   • 对比当前行与上一行的区域组合，若不同则标记为1，否则为0；通过SUM累积这些标记值，生成唯一的GroupID——连续的同区域行会拥有相同GroupID，非连续的同区域行GroupID不同
2. 聚合查询：
   • 按ADRESS_KEY和GroupID分组，同时保留区域字段，计算每组的最小/最大ADRESS_NO，得到正确的起止地址区间

执行结果 #

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