需要帮助将内部连接转换为外部连接

Question

SELECT a.p_cd, grand_total,day_total,grand_total_p,day_total_p
FROM ( select p_cd,IFNULL(count(*),0) grand_total 
       from rc_person rp, rc_payment_orders rpo 
       where rp.TRACK_NO=rpo.tracking_no 
       group by p_cd) a, 
     ( SELECT p_cd,IFNULL(count(*),0) day_total 
       from rc_person rp, rc_payment_orders rpo 
       where rp.TRACK_NO=rpo.tracking_no and app_dt='2022-04-14' 
       group by p_cd) b,
     ( SELECT p_cd,IFNULL(count(*),0) grand_total_p 
       from rc_person rp, rc_payment_orders rpo 
       where rp.TRACK_NO=rpo.tracking_no and STATUS='Processing' 
       group by p_cd) c,
     ( SELECT p_cd,IFNULL(count(*),0) day_total_p 
       from rc_person rp, rc_payment_orders rpo 
       where rp.TRACK_NO=rpo.tracking_no and STATUS='Processing' and app_dt='2022-04-14' 
       group by p_cd) d
WHERE a.p_cd=b.p_cd and b.p_cd=c.p_cd and c.p_cd=d.p_cd

Answer 1

测试这个：

SELECT p_cd,
       COUNT(*) grand_total,
       SUM(app_dt='2022-04-14') day_total,
       SUM(status='Processing') grand_total_p,
       SUM(status='Processing' AND app_dt='2022-04-14') day_total_p 
FROM rc_person rp
JOIN rc_payment_orders rpo ON rp.track_no=rpo.tracking_no 
GROUP BY p_cd