基于City ID关联查询国家信息与用户全量数据的SQL实现问询

要搞定这个需求，核心就是利用表之间的外键关联，把User、City、State、Country这四张表串联起来做查询。因为你需要保留所有用户数据，同时展示对应的国家信息，用LEFT JOIN会是最稳妥的选择——就算某个用户的关联数据（比如城市/州）有异常，也不会丢失用户本身的记录。

直接查询的SQL语句 #

SELECT 
    u.name AS 用户姓名,
    u.Age AS 用户年龄,
    u.CityID,
    c.ctyname AS 城市名称,
    s.Sname AS 州/省名称,
    co.Cname AS 国家名称,
    co.CountID AS 国家ID
FROM 
    User u
LEFT JOIN 
    City c ON u.CityID = c.CtyID
LEFT JOIN 
    State s ON c.Stateid = s.Stateid
LEFT JOIN 
    Country co ON s.CountID = co.CountID
-- 如果需要按特定CityID过滤，取消下面注释并替换值
-- WHERE u.CityID = 123; -- 这里的123换成你要查询的实际CityID

关键要点解释 #

• 表关联逻辑：用户表的CityID关联城市表的CtyID，城市表的Stateid关联州表的Stateid，州表的CountID关联国家表的CountID，这样就能通过用户的城市ID一路追溯到所属国家。
• LEFT JOIN的作用：和INNER JOIN不同，LEFT JOIN会保留左表（这里是User）的所有记录，哪怕右表没有匹配的数据。如果你的数据库中外键约束严格，确保所有用户的CityID都有对应城市记录，换成INNER JOIN结果也一样，但LEFT JOIN更容错。
• 别名使用：u、c、s、co是表的别名，写起来更简洁；字段别名（比如用户姓名）是为了让查询结果更容易看懂，你也可以换成英文别名。
• 过滤条件：如果只需要查询某个特定CityID的用户及对应国家信息，加上WHERE u.CityID = 目标ID即可；要是想返回所有用户的国家信息，删掉这条WHERE子句就行。

进阶：创建视图复用查询 #

如果这个查询需要经常使用，你可以把它创建成视图，之后直接查视图就方便多了：

CREATE VIEW 用户关联国家信息视图 AS
SELECT 
    u.name AS 用户姓名,
    u.Age AS 用户年龄,
    u.CityID,
    c.ctyname AS 城市名称,
    s.Sname AS 州/省名称,
    co.Cname AS 国家名称,
    co.CountID AS 国家ID
FROM 
    User u
LEFT JOIN 
    City c ON u.CityID = c.CtyID
LEFT JOIN 
    State s ON c.Stateid = s.Stateid
LEFT JOIN 
    Country co ON s.CountID = co.CountID;

之后查询的时候，直接执行：

SELECT * FROM 用户关联国家信息视图 WHERE CityID = 123;

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