我正在尝试从连接的表"Table2"中获取一个 COUNT(*),其中日期是常见的 PLUS 来自"Table1"的 COUNT(DISTINCT(CID)),并按共同的年月分组:
Table1
---------------
cid | date |
----|---------|
321 | 2016-01 |
----|---------|
423 | 2016-01 |
----|---------|
324 | 2016-01 |
----|---------|
546 | 2015-12 |
----|---------|
Table2
---------------
id | dateEnq |
----|---------|
3 | 2016-01 |
----|---------|
6 | 2016-01 |
----|---------|
24 | 2015-12 |
----|---------|
36 | 2015-12 |
----|---------|
MySQL 查询:
SELECT COUNT( DISTINCT (t1.cid) ) AS users,
SUBSTR(DATE(t1.date),1,7) AS month,
COUNT(t2.dateEnq) AS enquiries
FROM table1 t1
INNER JOIN table2 t2 ON SUBSTR(t2.dateEnq, 1, 7 ) = SUBSTR(t1.date, 1, 7 )
GROUP BY SUBSTR(DATE(t1.date),1,7)
这是我得到的结果,但查询值是错误的,我认为它没有计算 Table2 中的值,它们应该是每行 3、10、25
。如何从表2获取每月计数?
users | month | enquiries|
------|----------|-----------
7237 | 2015-10 | 8374 |
12597 | 2015-11 | 30066 |
12980 | 2015-12 | 15514 |
11305 | 2016-01 | 128169 |
你的"计数"被夸大了,因为它是部分交叉乘积。对于给定月份,t1
中的每一行都与 t2
中同月的每一行匹配。
一种选择是在执行联接之前获取计数。 例如,使用内联视图:
SELECT c1.users
, c1.month
, c2.enquiries
FROM ( SELECT COUNT(DISTINCT t1.cid) AS `users`
, SUBSTR(DATE(t1.date),1,7) AS `month`
FROM table1 t1
GROUP BY SUBSTR(DATE(t1.date),1,7)
) c1
JOIN ( SELECT COUNT(t2.dateEnq) AS `enquiries`
, SUBSTR(DATE(t2.date),1,7) AS `month`
FROM table2 t2
GROUP BY SUBSTR(DATE(t2.date),1,7)
) c2
ON c2.month = c1.month
ORDER BY c1.month
这不是获得此结果的唯一方法(甚至不一定是最佳方法)。还有其他查询模式可以实现等效的结果。
如果给定month
有可能enquiries
为零,那么要返回该零计数,我们可以调整该查询,使其成为外部连接,然后在外部查询中将任何 NULL 值(对于缺少的行)替换为零:
SELECT c1.users
, c1.month
, IFNULL(c2.enquiries,0) AS `enquiries`
FROM ( SELECT COUNT(DISTINCT t1.cid) AS `users`
, SUBSTR(DATE(t1.date),1,7) AS `month`
FROM table1 t1
GROUP BY SUBSTR(DATE(t1.date),1,7)
) c1
LEFT
JOIN ( SELECT COUNT(t2.dateEnq) AS `enquiries`
, SUBSTR(DATE(t2.date),1,7) AS `month`
FROM table2 t2
GROUP BY SUBSTR(DATE(t2.date),1,7)
) c2
ON c2.month = c1.month
ORDER BY c1.month