多表LEFT JOIN使用CONCAT拼接字段匹配时dailyweather表返回全空问题

问题原因 #

• 关联条件位置错误
  你把users.cityid = dailyweather.cityid的匹配规则写在了WHERE子句中，LEFT JOIN后无匹配的dailyweather字段全部为NULL，WHERE中的等值判断会直接过滤掉所有无匹配的行，同时该逻辑本属于两表关联的匹配规则，放在WHERE中会强制将LEFT JOIN转换为等效的INNER JOIN，不符合你的关联需求。
• 字符串拼接格式不匹配
  你当前拼接dailyweather字段的顺序是日-月-年，有两种情况会导致匹配失败：
  1. results.day存储格式为年-月-日（如2024-05-20），和拼接顺序完全相反
  2. dailyweather的day、month字段为数值类型，个位数不会自动补前导零（比如5月存储为数值5，拼接后为5，而results.day中对应值为05）
• 关联规则不完整
  dailyweather和前面的表关联需要同时满足日期匹配+城市ID匹配，两个条件都应该写在LEFT JOIN的ON子句中，才能正确关联到对应城市对应日期的天气数据。

修正方案 #

第一步：调整日期拼接逻辑 #

首先确认results.day的存储格式，对应调整CONCAT逻辑：

• 若results.day为年-月-日格式，调整拼接顺序同时补前导零：

CONCAT(dailyweather.year, '-', LPAD(dailyweather.month, 2, '0'), '-', LPAD(dailyweather.day, 2, '0'))

• 若results.day确实为日-月-年格式，仅补前导零即可：

CONCAT(LPAD(dailyweather.day, 2, '0'), '-', LPAD(dailyweather.month, 2, '0'), '-', dailyweather.year)

第二步：调整关联条件位置 #

将cityid的匹配规则移动到LEFT JOIN dailyweather的ON子句中，最终修正后的SQL示例（以results.day为年-月-日格式为例）：

SELECT results.*, users.cityid, dailyweather.*
FROM results
LEFT JOIN users ON results.id = users.id
LEFT JOIN dailyweather 
  ON results.day = CONCAT(dailyweather.year, '-', LPAD(dailyweather.month, 2, '0'), '-', LPAD(dailyweather.day, 2, '0'))
  AND users.cityid = dailyweather.cityid

验证方法 #

如果修改后仍无匹配数据，可以单独执行如下查询确认格式是否匹配：

-- 查看dailyweather拼接后的日期格式
SELECT CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) AS test_date FROM dailyweather LIMIT 1;
-- 查看results的日期存储格式
SELECT day FROM results LIMIT 1;

手动对比两个输出的字符串是否完全一致即可定位剩余问题。

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