在数据查询时,多个字段相加的情况下,当有一个字段为null,最终求和的结果会变成null,可以使用mysql自带的函数
IFNULL(exp1,exp2),exp1为字段名,exp2为字段为null时的赋值
SELECT
dealer.*, dealer.money + dealer.freeze_money + dealer.total_money AS total_amount,
IFNULL(
sum(firstOrder.order_price),
0
) + IFNULL(
sum(secondOrder.order_price),
0
) + IFNULL(
sum(thirdOrder.order_price),
0
) AS total_order_amount,
count(firstOrder.order_id) + count(secondOrder.order_id) + count(thirdOrder.order_id) AS total_order_nums
FROM
`5f_dealer_user` `dealer`
LEFT JOIN `5f_dealer_order` `firstOrder` ON `firstOrder`.`first_user_id` = `dealer`.`user_id`
LEFT JOIN `5f_dealer_order` `secondOrder` ON `secondOrder`.`second_user_id` = `dealer`.`user_id`
LEFT JOIN `5f_dealer_order` `thirdOrder` ON `thirdOrder`.`third_user_id` = `dealer`.`user_id`
WHERE
(
`dealer`.`wxapp_id` = 10001
AND `dealer`.`wxapp_id` = 10001
)
AND `dealer`.`is_delete` = 0
GROUP BY
`dealer`.`user_id`
ORDER BY
`total_amount` DESC,
`dealer`.`create_time` DESC