请教一下left join 使用问题,为什么这个语句结果达不到预期?

Question

SELECT ba_user.id                                                         as user_id,
       positions.name                                                     as posname,
       salary.id,
       salary.xingye_fee                                                  as xingyefee,
       salary.jianhang_fee                                                as jianhangfee,
       salary.part_fee                                                    as partfee,
       salary.temperature_fee                                             as temperaturefee,
       salary.oldpension_fee                                              as oldpensionfee,
       SUM(staff_yield.yield)                                             as total_yield,
       SUM(CASE WHEN wp_detail.type = 1 THEN avg_value * 0.54 ELSE 0 END) as dianfee,
       SUM(CASE WHEN wp_detail.type = 2 THEN avg_value * 4.6 ELSE 0 END)  as shuifee,
       SUM(avg_amount)                                                    as avg_amount

FROM `ba_user`
         left join ba_staff as staffs on ba_user.id = staffs.id
         left join ba_position as positions on staffs.position_id = positions.id
         left join ba_user_salary_log as salary
                   on ba_user.id = salary.user_id and salary.log_time >= 1682870400 and salary.log_time <= 1685462400
         left join ba_staff_yield as staff_yield
                   on ba_user.id = staff_yield.user_id and staff_yield.log_time >= 1682870400 and
                      staff_yield.log_time <= 1685462400
         left join ba_wp_detail as wp_detail on ba_user.id = wp_detail.user_id and wp_detail.log_time >= 1682870400 and
                                                wp_detail.log_time <= 1685462400
group by user_id
LIMIT 10

为什么包含left join ba_wp_detail 这个语句查询出来的total_yield和不带left join ba_wp_detail的查询出来的结果不一样，想要的结果就是每个用户对应的ba_wp_detail中sum的数据.如何处理

Answer 1

条件写on和写where后是不一样的。看看是不是这个原因导致的？