SQLite中基于LIST表关联CITY、COUNTRY的多表JOIN查询实现

核心问题根因 #

• LIST表的AreaID字段为双类型存储：既承载COUNTRY表的主键值，也承载CITY表的主键值，单一关联路径无法覆盖全部匹配场景
• 之前的单链路查询仅实现了AreaID = CITY.CityID的关联，未处理AreaID直接匹配国家ID的记录，导致LIST里前4条国家级的查询结果全为空值，自然无法返回符合预期的全量结果

正确查询语句 #

SELECT
  CITY.CityID,
  CITY.Name AS CityName,
  COALESCE(COUNTRY_DIRECT.CountryID, CITY.CountryID) AS CountryID,
  COALESCE(COUNTRY_DIRECT.Name, COUNTRY_VIA_CITY.Name) AS CountryName
FROM LIST
-- 匹配AreaID直接对应国家的场景
LEFT JOIN COUNTRY COUNTRY_DIRECT
  ON LIST.AreaID = COUNTRY_DIRECT.CountryID
-- 匹配AreaID直接对应城市的场景
LEFT JOIN CITY
  ON LIST.AreaID = CITY.CityID
-- 匹配到城市后，关联对应所属国家
LEFT JOIN COUNTRY COUNTRY_VIA_CITY
  ON CITY.CountryID = COUNTRY_VIA_CITY.CountryID

逻辑说明 #

• 全部使用LEFT JOIN而非INNER JOIN：保证LIST表中的所有记录都能出现在结果集中，不会因为关联不到地理信息丢失数据，满足全量提取要求；如果使用INNER JOIN，LIST中存储国家ID的记录因为无法匹配CITY表，会被直接过滤，完全不符合业务要求
• 使用COALESCE函数兼容两类国家数据来源：如果AreaID直接匹配到国家，优先展示直接匹配的国家数据；如果AreaID匹配到城市，则取城市所属的国家数据，避免同一条记录出现重复行
• 之前注释掉的直接按AreaID = COUNTRY.CountryID关联的逻辑，只要配合上面的双路径结构、加上数据来源判断，就不会出现关联错位问题

踩坑提示 #

• 不要直接把单条国家关联和城市链路关联并行写却不做来源判断：会导致城市记录同时匹配到直接关联的国家、以及城市所属的国家，产生重复冗余行
• 对于同一类ID字段的双用途存储场景，一定要先区分匹配优先级，再做关联，避免无匹配的空值行干扰最终结果

内容的提问来源于stack exchange，提问作者lukas-adrian