MySQL 多表关联统计，被关联表中无数据的记录正常显示并补0

Question

代码：

select
            source_name,
            channel_source_id,
            DATE(o.created_at) as date,
            COUNT(DISTINCT IF(status in(1,4), user_id, NULL)) as total_recharge_num,
            COUNT(DISTINCT IF(status in(1,4) AND is_usdt = 0, user_id, NULL)) as cny_total_recharge_num,
            COUNT(DISTINCT IF(status in(1,4) AND is_usdt = 1, user_id, NULL)) as usdt_total_recharge_num,
            SUM(IF(status in(1,4), pay_amount-refund_amount, 0)) as cny_total_paid_recharge_amount,
            SUM(IF(status in(1,4), pay_usdt_amount-usdt_refund_amount, 0)) as usdt_total_paid_recharge_amount,
            SUM(IF(status in(1,4) AND is_usdt = 0, pay_amount * (100 - pay_commission * 100) / 100-refund_amount, 0)) as cny_commission_amount,
            SUM(IF(status in(1,4) AND is_usdt = 1, pay_usdt_amount * (100 - pay_commission * 100) / 100-usdt_refund_amount, 0)) as usdt_commission_amount,
            SUM(IF(status in(1,4), refund_amount, 0)) as all_refund_amount,
            SUM(IF(status in(1,4), usdt_refund_amount, 0)) as all_usdt_refund_amount
        from(
            select id as cid,name as source_name from channel_source UNION select 0,'其他渠道'
            ) as c
         LEFT JOIN (select * FROM orders) o ON c.cid = o.channel_source_id 
         GROUP BY date,channel_source_id
            ORDER BY date desc;

channel_source 表：

现在代码的结果集：

我希望的结果集是

泰山-推特弹窗 1 2024-02-28 2 2 0 56.00
平头哥-百度竞价 4 2024-02-28 1 1 0 50.00
平头哥-百度竞价ocpc 11 2024-02-28 1 1 0 15.00
平头哥-UC竞价 12 2024-02-28 1 1 0 15.00
平头哥-UC竞价 12 2024-02-28 1 1 0 15.00
平头哥-青总 13 2024-02-28 0 0 0 0
其他渠道 0 2024-02-28 0 0 0 0

Answer 1

结合GPT给出回答如下请题主参考
假设有两个表：source表和order表，source表包含source_name和channel_source_id两个字段，order表包含created_at字段。

首先，我们需要进行多表关联，可以使用左连接（LEFT JOIN）方式保留source表中无数据的记录。然后使用IFNULL函数将缺失的数据替换为0。

下面是具体的查询语句：

SELECT
    s.source_name,
    s.channel_source_id,
    DATE(o.created_at) AS order_date,
    IFNULL(COUNT(o.created_at), 0) AS order_count
FROM source s
LEFT JOIN order o ON s.channel_source_id = o.channel_source_id
GROUP BY s.source_name, s.channel_source_id, order_date;

在这个例子中，将source表与order表进行左连接，连接条件为channel_source_id字段。然后使用GROUP BY对source_name、channel_source_id和order_date进行分组，这样可以统计每个source和日期的订单数量。使用IFNULL函数将缺失的记录的订单数量替换为0。

请根据实际情况调整表名和字段名。