SQL多表关联查询行数异常减少：如何正确合并countryCities等5张表

现有语句的核心问题 #

你的查询存在多个严重逻辑错误，是结果行数骤减的直接原因：

• 别名重复：countryRegions 和 countryRestrictions 都使用了cr作为别名，会导致字段引用歧义，数据库执行逻辑完全不符合预期
• 关联条件完全错误：
  • 关联countryStatus时的条件写为cp.id=cr.id，完全没有用到cs表的任何字段，关联逻辑混乱
  • 关联countryRestrictions时的条件同样写为cp.id=cr.id，没有用到限制表的字段，关联无效
  • 跨层级表直接用主键匹配：正常业务设计中，城市ID、省份ID、区域ID是各自独立的自增主键，只有极少量ID会巧合相等，用cc.id=cp.id、cp.id=cr.id的关联方式会过滤掉99%以上的行
• 连接类型不符合需求：INNER JOIN 只会保留所有关联表都匹配到的行，你countryStatus本身只有5行数据，就算关联条件正确，最终结果最多也只会有和这5行匹配的少量数据

正确实现方案 #

第一步：先梳理表的外键关联逻辑 #

常规的行政区划表设计的关联关系如下，你可以对照自己的表字段调整：

• countryCities（城市表）存province_id字段，关联countryProvinces（省份表）的主键id
• countryProvinces（省份表）存region_id字段，关联countryRegions（区域表）的主键id
• countryStatus（状态表）、countryRestrictions（限制表）存target_id字段，关联对应行政区划（城市/省份/区域）的主键id

第二步：编写修正后的查询 #

如果需要保留所有城市的基础数据，哪怕没有对应的状态、限制数据也展示，用LEFT JOIN代替INNER JOIN，示例语句如下：

SELECT 
  cc.id AS city_id, 
  cc.name AS city_name, 
  cc.is_city, 
  cp.id AS province_id, 
  cp.name AS province_name, 
  cp.key AS province_key, 
  cr.id AS region_id, 
  cr.name AS region_name, 
  cr.long_name AS region_long_name, 
  cr.key AS region_key,
  cs.id AS status_id, 
  cs.status,
  cres.id AS restriction_id,
  cres.start, 
  cres.end, 
  cres.travelAllowed
FROM countryCities AS cc
-- 关联省份表
LEFT JOIN countryProvinces cp ON cc.province_id = cp.id
-- 关联区域表
LEFT JOIN countryRegions cr ON cp.region_id = cr.id
-- 关联状态表，此处假设状态关联到城市，若关联省份则改为 cp.id = cs.target_id
LEFT JOIN countryStatus cs ON cc.id = cs.target_id
-- 关联限制表，别名用cres避免重复，若关联省份/区域自行调整关联字段
LEFT JOIN countryRestrictions cres ON cc.id = cres.target_id

注意事项 #

如果你的需求是仅保留同时存在状态、限制数据的行，可以将对应表的LEFT JOIN改为INNER JOIN即可。

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