查询用户消费订单表

 

SELECT
u.user_id,
u.store_id,
u.mobile,
COALESCE ( SUM( o.pay_price ), 0 ) AS total_consumption
FROM
sh_user u
LEFT JOIN sh_recharge_order o ON u.user_id = o.user_id AND o.pay_status = 20
WHERE
u.store_id = 10013 AND u.mobile != ''
GROUP BY
u.user_id
ORDER BY
total_consumption desc; 

订单表的筛选条件需要放在 on链接上，否则会丢失用户表数据