与 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 。 如果我移动代码并从潜在客户表中进行选择，我会在所有点击中获得正确的潜在客户 bot 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，但我还没有找到一种方法来从中获取这两个计数。

（这是此的后续内容之前的问题，但由于我没有询问计数，所以我发布了一个新问题。）

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

Answer 2

我使用的代码是根据 Quassnoi 的建议构建的：

SELECT  date,
        COALESCE(ccomm, 0) AS click_commission, COALESCE(ccnt, 0) AS click_count,
        COALESCE(lcomm, 0) AS lead_commision, COALESCE(lcnt, 0) AS lead_count,
        COALESCE(ccomm, 0) + COALESCE(lcomm, 0) as total_commission
FROM    date d
LEFT JOIN
        (
        SELECT  DATE(time) AS lead_date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
        FROM    lead
        GROUP BY
                lead_date
        ) l
ON     lead_date = date
LEFT JOIN
        (
        SELECT  DATE(time) AS click_date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
        FROM    click
        GROUP BY
                click_date
        ) с
ON      click_date =  date