MySQL中根据日期的两个表组合 [英] Group sum from two tables according to date in MySQL

问题描述

我有两个不同的表，铅和点击。我想查询MySQL，以获得按日期排序的点击和线索产生的佣金总和。

Lead

  id | date |佣金
 1 | 2009-06-01 | 400 
 2 | 2009-06-01 | 300 
 3 | 2009-06-03 | 350 
  

点击： strong>

  id | date |佣金
 1 | 2009-06-01 | 1 
 2 | 2009-06-03 | 2 
 3 | 2009-06-03 | 1 
  

I想要创建一个给我的查询（如果可能的话也可以得到没有生成线索或点击的日期）：

 日期|佣金点击|佣金牵头|总佣金
 2009-06-01 | 1 | 700 | 701 
 2009-06-02 | 0 | 0 | 0 
 2009-06-02 | 3 | 350 | 353 
  

（实际数据库中的日期实际上是日期时间）。

我想我必须结合：

  SELECT count（*）as number_clicks，sum（click。佣金）作为sum_clicks，
 date（click.time）as click_date from click group by click_date order by click_date 
  

与：

  SELECT count（*）as number_leads，sum（lead.commission）as sum_leads，
 date（lead.time）as lead_date from lead group by lead_date order by lead_date 
  

但是我不能让他们一起工作。

解决方案

这不会得到零的日期，因为你需要一个日期表或者循环遍历日期的存储过程。一种方法是从联合查询（未经测试）中选择一个子选项：

  SELECT commission_date，SUM（click_commission），SUM （$）
 FROM（SELECT DATE（click.time）AS commission_date，click.com submit AS click_commission，
 0 AS lead_commission，click.commission AS total_commission 
 FROM click 
 UNION ALL 
选择日期（lead.time）AS佣金日期，0 AS click_commission，
 lead.commission AS lead_commission，lead.commission AS total_commission 
 FROM lead）AS foo 
 GROUP BY commission_date 
 ORDER BY commission_date 
  

I have two different tables, lead and click. I would like to query MySQL to get the sum of the commissions generated by clicks and leads ordered by date.

Lead:

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

Click:

id|date      |commission
 1|2009-06-01|1
 2|2009-06-03|2
 3|2009-06-03|1

I would like to create a query that gives me (and if possible also gets date where no lead or click has been generated):

date      |commission click|commission lead|total commission
2009-06-01|               1|            700|             701
2009-06-02|               0|              0|               0
2009-06-02|               3|            350|             353

(The date is actually datetime in the real database.)

I guess I have to combine:

SELECT count(*) as number_clicks, sum(click.commission) as sum_clicks,
 date(click.time) as click_date from click group by click_date order by click_date

With:

SELECT count(*)as number_leads, sum(lead.commission) as sum_leads,
 date(lead.time) as lead_date from lead group by lead_date order by lead_date

But I can not get them to work together.

解决方案

This doesn't get dates with zeroes, for that you'll either need a dates table or a stored procedure to loop through dates. One way to do it is a subselect from a union query (untested):

SELECT commission_date, SUM(click_commission), SUM(lead_commission), SUM(total_commission)
FROM (SELECT DATE(click.time) AS commission_date, click.commission AS click_commission,
             0 AS lead_commission, click.commission AS total_commission
      FROM click
      UNION ALL
      SELECT DATE(lead.time) AS commission_date, 0 AS click_commission,
             lead.commission AS lead_commission, lead.commission AS total_commission
      FROM lead) AS foo
GROUP BY commission_date
ORDER BY commission_date

这篇关于MySQL中根据日期的两个表组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！