如何编写SELECT语句关联CANADA与CLIENT表，获取客户省市区信息？

需求说明 #

现有两张业务表：

• CANADA：存储国家、省、市层级数据，字段包含canada_id、name（地区名称）、id（省/市的唯一标识）、parent_id（上级地区的id）
• CLIENT：存储客户关联信息，字段包含client_id、name（客户名称）、province_id（客户所属省的id）、city_id（客户所属市的id）

需要编写查询语句，返回包含客户名称、省份ID、省份名称、城市ID、城市名称的结果集。

解决方案SQL #

SELECT
    c.name AS 客户名称,
    c.province_id,
    prov.name AS 省份名称,
    c.city_id,
    city.name AS 城市名称
FROM
    CLIENT c
LEFT JOIN
    CANADA prov ON prov.id = c.province_id
LEFT JOIN
    CANADA city ON city.id = c.city_id;

逻辑说明 #

1. 以CLIENT表为主表，通过两次LEFT JOIN关联CANADA表：
   • 第一次关联（别名prov）：用CLIENT.province_id匹配CANADA.id，获取对应省份名称
   • 第二次关联（别名city）：用CLIENT.city_id匹配CANADA.id，获取对应城市名称
2. 使用LEFT JOIN可确保即使客户未填写省/市信息（对应字段为NULL），该客户记录仍会被保留；如果业务要求客户必须关联省/市，可替换为INNER JOIN
3. 为两次关联的CANADA表设置不同别名，避免字段名称冲突

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