mysql 将 2 个表与一个公共(public)的 group by 列组合起来

Question

我有 2 个带有日期的表，采用时间戳格式，我必须计算每周总共有多少条记录...

...并且只需一次查询即可得到结果...

所以我会用例子更好地解释它:

2个表，第一个:

table name: visitor

ID  | time  | ref     (now i put in fake timestamp in time column sorry)
--------------------
1   | 3455  | john
2   | 3566  | ted     (week 40)
3   | 8353  | ted     (week 38)
4   | 6673  | katy
5   | 6365  | ted     (week 40)
6   | 4444  | john
7   | 3555  | ted     (week 40)

和第二个(非常相似):

table name: users

ID  | time  | ref     (now i put in fake timestamp in time column sorry)
--------------------
1   | 3455  | ted     (week 41)
2   | 3566  | ted     (week 42)
3   | 8353  | ted     (week 40)
4   | 6673  | katy
5   | 6365  | ted     (week 41)
6   | 4444  | john
7   | 3555  | ted     (week 38)
8   | 6789  | ted     (week 43)

我做了这个查询，得到了这个结果:

SELECT WEEK(FROM_UNIXTIME(time)) AS week, COUNT( * ) AS tot
FROM visitor WHERE ref='ted' GROUP BY week

表格结果#1

week | tot
----------
38   |  1
40   |  3
43   |  1

我为第二个表做了一些:

SELECT WEEK(FROM_UNIXTIME(time)) AS week, COUNT( * ) AS totuser
FROM users WHERE ref='ted' GROUP BY week

表格结果#2

week | totuser
----------
38   |  1
40   |  1
41   |  2
42   |  1

但我想只用一个查询就能得到这个结果:

week | tot | totusers
----------------------  (when both are 0 doent appear -like 39 for example-)
38   |  1  |   1
40   |  3  |   1
41   |  0  |   2
42   |  0  |   1
43   |  1  |   0

我知道我必须使用 LEFT JOIN、GROUP BY 和 IFNULL，但我总是做错事，而且我无法弄清楚。

按周描述排序

感谢您的帮助。

Answer 1

从技术上讲，您想要的是完全外连接，但 MySQL 不支持这一点。我使用 union all 和 group by 来实现此目的:

SELECT weeek, SUM(visitors) as visitors, SUM(users) as users
FROM ((SELECT WEEK(FROM_UNIXTIME(time)) AS week, COUNT( * ) AS visitors, 0 as users
       FROM visitor
       WHERE ref='ted'
       GROUP BY week
      ) UNION ALL
      (SELECT WEEK(FROM_UNIXTIME(time)) AS week, 0, COUNT( * )
       FROM users
       WHERE ref ='ted'
       GROUP BY week
      )
     ) w
GROUP BY week
ORDER BY week;

注意:正如您的数据所示，这仅包括有访问者或用户的周。如果您想要几周都没有，最好从包含您想要的所有周的表格(某种日历表)开始。