LEFT JOIN ... ON是可以添加条件的,找到满足条件的记录再进行JOIN操作。
WHERE: 对join后的进行筛选
SELECT
p.* ,
fm.id AS fm_id,
fm.name AS fm_name,
fm.ppath AS fm_ppath,
fm.file_name AS fm_file_name,
fm.belong_obj AS fm_belong_obj,
dir.name AS fm_save_path_name
FROM
prj_build_progress p
LEFT JOIN files_management fm ON fm.belong_obj = p.id AND (fm.del_flag != '1' OR fm.del_flag IS NULL )
LEFT JOIN files_management dir ON fm.ppath = dir.path
WHERE p.id =
ORDER BY fm.update_time,fm.create_time
额外的,如上代码,判断 != '1',需要考虑到NULL的情况(同时集合实际业务)