在SQL中为每一天获取数据 [英] Getting data for every single day in SQL
问题描述
我目前有以下SQL语句
MySQL查询:
SELECT
c.day,
COUNT(*)
从
日历c
JOIN
访问者d
在DAYOFMONTH(d。创建)= c.day
WHERE
c.day BETWEEN DAYOFMONTH('2012-10-01')和DAYOFMONTH('2012-10-31')
AND
site_id = 16
GROUP BY
DAYOFMONTH(d.created)
ORDER BY
DAYOFMONTH(d.created)
我的表:
日历
id |天
---------
1 | 1
2 | 2
3 | 3
...
31 | 31
访客
id | site_id |创建
-----------------------------------
1 | 16 | 2012-10-18 11:14:39
2 | 16 | 2012-10-18 11:15:17
3 | 11 | 2012-10-18 11:49:14
4 | 11 | 2012-10-18 11:49:43
5 | 16 | 2012-10-19 11:54:37
6 | 1 | 2012-10-19 05:56:31
7 | 2 | 2012-10-19 05:57:56
我已经创建了这个表,日历回答,但我似乎仍然得到相同的信息。我只收到我有数据的日期。
day | COUNT(*)
---------------------
18 | 2
19 | 1
我还需要检索 0
没有数据的日期。
更新:
我试过这是:
/ pre>
SELECT *
FROM calendar c
LEFT JOIN访客d
ON DAYOFMONTH(d.created)= c.day
和
SELECT *
FROM calendar c
LEFT JOIN访客d
ON DAYOFMONTH(d.created)= c.day
WHERE site_id = 16
我可以确认
site_id = 16
肯定是杀死结果的一个。解决方案使用
LEFT JOIN
而不是INNER JOIN
p>
SELECT ...
FROM calendar c
LEFT JOIN访客d
在DAYOFMONTH(d。创建)= c.day
WHERE ...
INNER JOIN
仅检索其他表中至少有一个匹配的行,而LEFT JO IN
检索 lefthand side 表中定义的所有行,无论其他表是否匹配。
更新1
SELECT c.day,
COUNT(*)
FROM calendar c
LEFT JOIN
(
SELECT *
FROM访客
WHERE site_id = 16
)d ON DAYOFMONTH(d.created )= c.day
WHERE c.day BETWEEN DAYOFMONTH('2012-10-01')和DAYOFMONTH('2012-10-31')
组由DAYOFMONTH(c.day)
订单DAYOFMONTH(c.day)
SELECT c.day,
COUNT(site_id)
从日历c
LEFT JOIN
(
SELECT *
FROM访客
WHERE site_id = 16
) d日DOFOFON(d.created)= c.day
WHERE c.day BETWEEN DAYOFMONTH('2012-10-01')和DAYOFMONTH('2012-10-31')
GROUP BY c.day
订单由c.day
我们不能使用
COUNT(*)
code> 1 每天。我们也不应在c.day的GROUP BY
和ORDER BY 中使用
DAYOFMONTH
/ code>因为它已经是我们需要的。I currently have the following SQL statement
MySQL Query:
SELECT c.day, COUNT(*) FROM calendar c JOIN visitors d ON DAYOFMONTH(d.created) = c.day WHERE c.day BETWEEN DAYOFMONTH('2012-10-01') AND DAYOFMONTH('2012-10-31') AND site_id = 16 GROUP BY DAYOFMONTH(d.created) ORDER BY DAYOFMONTH(d.created)
My Tables:
Calendar id | day --------- 1 | 1 2 | 2 3 | 3 ... 31 | 31 Visitors id | site_id | created ----------------------------------- 1 | 16 | 2012-10-18 11:14:39 2 | 16 | 2012-10-18 11:15:17 3 | 11 | 2012-10-18 11:49:14 4 | 11 | 2012-10-18 11:49:43 5 | 16 | 2012-10-19 11:54:37 6 | 1 | 2012-10-19 05:56:31 7 | 2 | 2012-10-19 05:57:56
I have created the table, calendar as prescribed in this answer but I seem to still get the same information. I am only getting the dates where I have data.
day | COUNT(*) --------------------- 18 | 2 19 | 1
I need to also retrieve
0
on the dates that have no data.UPDATE:
I tried this: SELECT * FROM calendar c LEFT JOIN visitors d ON DAYOFMONTH(d.created) = c.day
and
SELECT * FROM calendar c LEFT JOIN visitors d ON DAYOFMONTH(d.created) = c.day WHERE site_id = 16
I can confirm that the
site_id = 16
is certainly the one killing the results.解决方案use
LEFT JOIN
instead ofINNER JOIN
SELECT ... FROM calendar c LEFT JOIN visitors d ON DAYOFMONTH(d.created) = c.day WHERE...
INNER JOIN
retrieves only rows which has atleast one match on the other table whileLEFT JOIN
retrieves all rows define on the lefthand side table whether it has a match or none on the other table(s).UPDATE 1
SELECT c.day, COUNT(*) FROM calendar c LEFT JOIN ( SELECT * FROM visitors WHERE site_id = 16 ) d ON DAYOFMONTH(d.created) = c.day WHERE c.day BETWEEN DAYOFMONTH('2012-10-01') AND DAYOFMONTH('2012-10-31') GROUP BY DAYOFMONTH(c.day) ORDER BY DAYOFMONTH(c.day)
**UPDATE by Thorpe Obazee
SELECT c.day, COUNT(site_id) FROM calendar c LEFT JOIN ( SELECT * FROM visitors WHERE site_id = 16 ) d ON DAYOFMONTH(d.created) = c.day WHERE c.day BETWEEN DAYOFMONTH('2012-10-01') AND DAYOFMONTH('2012-10-31') GROUP BY c.day ORDER BY c.day
We cannot use
COUNT(*)
since it will return1
every day. We also should not useDAYOFMONTH
on c.day in theGROUP BY
andORDER BY
since it is already what we need.这篇关于在SQL中为每一天获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!