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 = #{id}
ORDER BY fm.update_time,fm.create_time

额外的,如上代码,判断 != '1',需要考虑到NULL的情况(同时集合实际业务)