如何用SQL查询获取各市政当局对应年份的有效实体

我需要通过SQL查询获取市政当局的前身/后续关联关系，输入表MUNICIPALMERGE的结构和数据如下：

目标是生成一张表，展示每个年份、每个市政当局对应的有效市政当局ID（即year + municipality_id → valid_municipality_id）。但我尝试的查询中，valid_municipality_id列出现大量空值，请问如何正确实现？

原尝试的SQL查询：

WITH
  YEARS AS
  (
    SELECT
      1990 AS YEAR
    UNION ALL
    SELECT
      YEAR+1
    FROM
      YEARS
    WHERE
      YEAR+1<=2005--YEAR(GETDATE())
  )
  --SELECT * FROM YEARS;
, YEARS_MUNICIPALITY AS
  (
    SELECT
      Y.YEAR
    , MM.MUNICIPALITY_ID
    , ISNULL(MM.PREDECESSOR_MUNICIPALITY_ID, MM.MUNICIPALITY_ID) AS PREDECESSOR_MUNICIPALITY_ID
    FROM
      YEARS Y
    INNER JOIN
      MUNICIPALMERGE MM
    ON
      Y.YEAR BETWEEN MM.VALID_YEAR_FROM AND MM.VALID_YEAR_TO
  )
  --SELECT * FROM YEARS_MUNICIPALITY;
, ALL_YEARS_MUNICIPALITY AS
  (
    SELECT
      Y.YEAR
    , MM.MUNICIPALITY_ID
    , MM.PREDECESSOR_MUNICIPALITY_ID
    FROM
      YEARS Y
    CROSS JOIN
      MUNICIPALMERGE MM
  )
--SELECT * FROM ALL_YEARS_MUNICIPALITY;
SELECT
  AYM.YEAR
, AYM.MUNICIPALITY_ID
, YM.MUNICIPALITY_ID AS VALID_MUNICIPALITY_ID
FROM
  ALL_YEARS_MUNICIPALITY AYM
LEFT JOIN
  YEARS_MUNICIPALITY YM
ON
  AYM.YEAR=YM.YEAR
AND AYM.MUNICIPALITY_ID=YM.PREDECESSOR_MUNICIPALITY_ID
ORDER BY
  AYM.MUNICIPALITY_ID
, AYM.YEAR;

问题分析 #

原查询的核心问题是没有处理链式的市政当局继承关系（比如1000→1001→1002），且关联逻辑仅匹配了直接前身，导致很多年份的市政当局无法找到对应有效ID，出现空值。此外，ALL_YEARS_MUNICIPALITY的交叉连接会生成很多无效组合，进一步加剧空值问题。

正确实现方案 #

使用递归CTE遍历每个市政当局的所有历史关联，结合年份匹配，最终生成完整的映射表：

WITH
  -- 生成1990到2005的年份序列
  YEARS AS (
    SELECT 1990 AS YEAR
    UNION ALL
    SELECT YEAR + 1 FROM YEARS WHERE YEAR + 1 <= 2005
  ),
  -- 递归遍历市政当局的所有前身/后续关系，构建完整的历史链
  MUNICIPAL_HISTORY AS (
    -- 基础成员：初始市政当局（无前身的）
    SELECT
      municipality_id AS original_id,
      municipality_id AS valid_id,
      valid_year_from,
      valid_year_to
    FROM MUNICIPALMERGE
    WHERE predecessor_municipality_id IS NULL
    
    UNION ALL
    
    -- 递归成员：关联后续的市政当局
    SELECT
      mh.original_id,
      mm.municipality_id AS valid_id,
      mm.valid_year_from,
      mm.valid_year_to
    FROM MUNICIPAL_HISTORY mh
    JOIN MUNICIPALMERGE mm ON mh.valid_id = mm.predecessor_municipality_id
  ),
  -- 生成所有年份与所有市政当局的组合
  ALL_YEAR_MUNI AS (
    SELECT
      y.YEAR,
      mm.municipality_id
    FROM YEARS y
    CROSS JOIN MUNICIPALMERGE mm
  )
-- 关联历史链，找到每个年份、市政当局对应的有效ID
SELECT
  aym.YEAR,
  aym.municipality_id,
  -- 如果当前市政当局在该年份本身有效，则优先用自己；否则找历史链中的有效ID
  COALESCE(
    (SELECT valid_id FROM MUNICIPALMERGE WHERE municipality_id = aym.municipality_id AND aym.YEAR BETWEEN valid_year_from AND valid_year_to),
    (SELECT valid_id FROM MUNICIPAL_HISTORY WHERE original_id = aym.municipality_id AND aym.YEAR BETWEEN valid_year_from AND valid_year_to)
  ) AS valid_municipality_id
FROM ALL_YEAR_MUNI aym
ORDER BY aym.municipality_id, aym.YEAR;

结果说明 #

这个查询会生成符合需求的完整映射，关键逻辑如下：

• 如果市政当局在目标年份处于自身有效区间内，valid_municipality_id直接取自身ID
• 如果市政当局已被合并，会递归找到其后续的有效市政当局ID
• 对于无继承关系的市政当局（如1003），所有年份的有效ID均为自身

内容的提问来源于stack exchange，提问作者Michael S.