mysql - 从两个表中按日期分组选择

我有两个表:

表t1:

id | date_click
 1 | 2016-02-31 17:17:23
 2 | 2016-03-31 12:11:21
 3 | 2016-03-31 13:13:23

因此，我想从该表中获取每天的计数字段Id。

为此，我使用下一个查询:

SELECT date_format(date_click, '%Y-%m-%d') as date_click_event
     , COUNT(id) as count_click
  FROM t1
 GROUP
    BY date_click_event
 ORDER
    BY date_click_event DESC;

很好

因此，下一张表是t2。

id | count | date_sent
 1 |    33 | 2016-02-31 11:12:23
 2 |    22 | 2016-03-31 14:11:22
 3 |    11 | 2016-03-31 13:12:13

要从该表中按日期选择数据，我使用下一个查询:

SELECT date_format(date_sent, '%Y-%m-%d') as date_sent_push
     , SUM(count) as count_sent
  FROM t2
 GROUP
    BY date_sent_push
 ORDER
    BY date_sent_push DESC
 LIMIT 100;

这也很好。因此，我的目的是将这两个查询合并为一个SELECT，接下来我可以在php中编写一个表，其中包含表Id中按日期划分的t1和计数中的count表中按日期划分的t2字段。

当我尝试下一个查询时:

SELECT date_format(t2.date_sent, '%Y-%m-%d') AS date_sent_push
     , SUM(t2.count) as count_sent
     , COUNT(t1.id) as count_click
  FROM t2
     , t1
 WHERE date_format(t2.date_sent, '%Y-%m-%d') = date_format(t1.date_click, '%Y-%m-%d')
 GROUP
    BY date_sent_push
 ORDER
    BY date_sent_push
  DESC
 LIMIT 100;

不行我做错了什么？

最佳答案

首先，您应该对这些结果进行UNION，然后按天分组并选择汇总字段。您也可以加入这些查询，但是如果某些天在两个表之一中未命中，则可能是一个问题:

SELECT date_sent_push,
       MAX(count_click) as count_click,
       MAX(count_sent) as count_sent
FROM
(SELECT date_format(date_click, '%Y-%m-%d') as date_sent_push
     , COUNT(id) as count_click
     , NULL as count_sent
  FROM t1
  GROUP BY date_sent_push
  UNION ALL
  SELECT date_format(date_sent, '%Y-%m-%d') as date_sent_push
     , NULL as count_click
     , SUM(count) as count_sent
  FROM t2
  GROUP
    BY date_sent_push
) as t3
GROUP BY date_sent_push

SQL fiddle demo

关于mysql - 从两个表中按日期分组选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37044565/