MySQL中如何用子查询结果替代原有JOIN改写SQL语句

没问题，我来帮你搞定这条SQL的改写需求。你需要把原来的第二个内连接替换成自定义的子查询，其实只需要把这个子查询当作临时表来关联就行，正好能满足你要求的LCZ.country_id和子查询返回的country_id匹配的条件。

最终改写后的SQL代码 #

SELECT 
    USR.id AS hq_user_id, 
    USR.local_user_id, 
    LCZ.id AS localization_id, 
    LCZ.description AS description, 
    LCZ_COUNTRY_LEVEL.country_id, 
    LCZ_COUNTRY_LEVEL.country_description
FROM 
    Localization AS LCZ 
INNER JOIN 
    User AS USR ON USR.localization_id = LCZ.id 
INNER JOIN 
    (
        -- 你提供的筛选国家层级Localization的子查询，调整了字段别名以匹配原查询的输出需求
        SELECT 
            LCZ2.id AS country_id,
            LCZ2.description AS country_description,
            LCZ2.country_id AS country_match_id
        FROM 
            Localization AS LCZ2 
        WHERE 
            LCZ2.region_id IS NULL 
            AND LCZ2.province_id IS NULL 
            AND LCZ2.city_id IS NULL 
            AND LCZ2.district_id IS NULL 
            AND LCZ2.town_id IS NULL 
            AND LCZ2.village_id IS NULL
    ) AS LCZ_COUNTRY_LEVEL 
    ON LCZ.country_id = LCZ_COUNTRY_LEVEL.country_match_id -- 核心关联条件
WHERE 
    USR.local_user_id = 999

关键说明 #

• 我把你提供的子查询包装成了临时表LCZ_COUNTRY_LEVEL，并调整了字段别名来适配原查询的输出要求：
  • 将国家层级Localization的id命名为country_id，对应原查询中LCZ_COUNTRY_LEVEL.id AS country_id的输出
  • 将description命名为country_description，对应原查询的LCZ_COUNTRY_LEVEL.description AS country_description
  • 新增country_match_id字段，用来和主查询里LCZ表的country_id做关联，确保连接逻辑正确
• 完全移除了你想要替换的第二个内连接，用子查询的方式实现了相同的关联效果，同时保留了原查询的所有输出字段

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