mysql - 与 Count 合并或与 Sum 合并 - MySQL

Question

我想在一个查询中组合三个表 - 日期、潜在客户和点击。

表格如下所示:

日期:

|date|

铅:

id|time|commission

点击:

id|time|commission

表格日期只是存储日期，在没有点击或引导的情况下获取日期时使用。

所以如果我们在表中有以下数据:

日期:

2009-06-01
2009-06-02
2009-06-03

铅:

1|2009-06-01|400
2|2009-06-01|300
3|2009-06-03|350

点击:

1|2009-06-01|1
2|2009-06-03|2
3|2009-06-03|2
4|2009-06-03|0

我想获取日期、点击次数、点击产生的佣金(有些点击不给佣金)、潜在客户数量、潜在客户产生的佣金和总佣金。因此，对于上面的表格，我想得到:

2009-06-01|1|1|2|700|701|
2009-06-02|0|0|0|0|0
2009-06-03|3|4|1|350|354|

我尝试过以下联合:

 SELECT  
    campaign_id, 
    commission_date,  
    SUM( click_commission ) AS click_commission,
    click,
    SUM( lead_commission ) AS lead_commission ,  
    lead,
    SUM( total_commission ) as total_commission
    FROM(
        SELECT  
            click.campaign_id AS campaign_id, 
            DATE( click.time ) AS commission_date, 
            click.commission AS click_commission, 
            (SELECT count(click.id) from click GROUP BY date(click.time)) as click,
            0 as lead_commission,
            0 as lead,
            click.commission AS total_commission
        FROM click
        UNION ALL
        SELECT 
            lead.campaign_id AS campaign_id, 
            DATE( lead.time ) AS commission_date, 
            0 as click_commission,
            0 as click,
            lead.commission AS lead_commission, 
            lead.id as lead,
            lead.commission AS total_commission
        FROM lead
        UNION ALL
        SELECT 
            0 AS campaign_id, 
            date.date AS commission_date, 
            0 AS click_commission, 
            0 as click,
            0 AS lead_commission, 
            0 as lead,
            0 AS total_commission
        FROM date 
    ) AS foo 
    WHERE commission_date BETWEEN '2009-06-01' AND '2009-07-25' 
    GROUP BY  commission_date 
    ORDER BY commission_date LIMIT 0, 10

但这并不能同时计算点击次数和潜在客户数量，上面的代码在所有潜在客户上给出了正确的点击量 bot 0。如果我四处移动代码并从潜在客户表中进行选择，我会在所有点击中获得正确的潜在客户机器人 0。我一直无法找到一种方法来从查询中获取这两个计数。

所以我尝试了左连接:

SELECT
    date.date as date, 
    count( DISTINCT click.id ) AS clicks, 
    sum(click.commission) AS click_commission, 
    count( lead.id ) AS leads, 
    sum(lead.commission) AS lead_commission
FROM date
LEFT JOIN click ON ( date.date = date( click.time ) )
LEFT JOIN lead ON ( date.date = date( lead.time ) )
GROUP BY date.date
LIMIT 0 , 30 

此查询的问题是，如果在某个日期有多次点击或潜在客户，它将返回预期值 * 2。因此，在 2009 年 6 月 1 日，它将返回 1400，而不是潜在客户佣金的预期 700。

所以在 UNION 中，我的计数有问题，在左连接中，SUM 不起作用。

如果可能的话，我真的很想坚持使用 UNION，但我还没有找到从中获得这两个计数的方法。

(这是对 this 较早问题的跟进，但由于我没有要求其中的计数，所以我发布了一个新问题。)

Answer 1

SELECT  date,
        COALESCE(lcomm, 0), COALESCE(lcnt, 0),
        COALESCE(ccomm, 0), COALESCE(ccnt, 0),
        COALESCE(ccomm, 0) + COALESCE(lcomm, 0),
        COALESCE(ccnt, 0) + COALESCE(lcnt, 0)
LEFT JOIN
        (
        SELECT  date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
        FROM    leads
        GROUP BY
                date
        ) l
ON      l.date = d.date
LEFT JOIN
        (
        SELECT  date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
        FROM    clicks
        GROUP BY
                date
        ) с
ON      c.date = d.date
FROM    date d