从两个表中按日期分组选择数据

Question

I have two tables:

Table 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中写入一个表格，该表格按日期计算来自表t1的Id数量和来自表t2的count字段的数量。

当我尝试下一个查询时：

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;

这个不起作用。我做错了什么？

- user3499878

---

你说的“不工作”是什么意思？是出现了错误吗？还是得到了错误的结果？最好展示一下期望的结果。 - ScaisEdge

Answer 1

首先，您应该将这些结果UNION起来，然后按天分组并选择聚合字段。您也可以JOIN这些查询，但如果其中一个表中缺少某些天，则可能会出现问题。

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

- valex

Answer 2

use this code

(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)
UNION
(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)

- yasin pathan

---

返回错误：＃1222-使用的SELECT语句具有不同数量的列，但两个表中的日期计数相等。 - user3499878

联合查询需要相同数量的列，因此第一个查询语句中的列数必须等于第二个查询语句中的列数。 - Dipanwita Kundu