基于post表User ID从city表查询对应城市名称的SQL方案求助

首先明确你给出的表结构：

• city表：包含city_id（主键）、city_name（城市名称）
• post表：包含user_id（发布帖子的用户ID），通常还会有post_id作为主键

这里分两种常见场景给出解决方案：

场景1：存在用户表（关联用户与城市） #

通常用户的城市信息会存在单独的user表中（结构：user_id主键、city_id关联city表），此时需要通过三次关联查询：

MySQL/MariaDB 生成JSON #

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'post_id', p.post_id,
        'user_id', p.user_id,
        'city_name', c.city_name
    )
) AS post_city_result
FROM post p
JOIN user u ON p.user_id = u.user_id
JOIN city c ON u.city_id = c.city_id;

PostgreSQL 生成JSON #

SELECT json_agg(
    json_build_object(
        'post_id', p.post_id,
        'user_id', p.user_id,
        'city_name', c.city_name
    )
) AS post_city_result
FROM post p
JOIN "user" u ON p.user_id = u.user_id
JOIN city c ON u.city_id = c.city_id;

场景2：用户ID直接关联城市ID（不常见设计） #

如果post表的user_id直接对应city表的city_id（这种设计逻辑上不太合理，但按需求实现）：

MySQL/MariaDB 生成JSON #

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'post_id', p.post_id,
        'user_id', p.user_id,
        'city_name', c.city_name
    )
) AS post_city_result
FROM post p
JOIN city c ON p.user_id = c.city_id;

PostgreSQL 生成JSON #

SELECT json_agg(
    json_build_object(
        'post_id', p.post_id,
        'user_id', p.user_id,
        'city_name', c.city_name
    )
) AS post_city_result
FROM post p
JOIN city c ON p.user_id = c.city_id;

补充说明 #

• 若要保留无对应城市的帖子，将JOIN替换为LEFT JOIN
• 不同数据库的JSON函数语法有差异，需根据实际使用的数据库调整
• 确保关联字段的数据类型一致（如user_id和city_id都是整数类型）

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