在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 没有数据的日期。

更新：

 我试过这是：
 SELECT * 
 FROM calendar c 
 LEFT JOIN访客d 
 ON DAYOFMONTH（d.created）= c.day 
  / pre> 
 
 
和
  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）
  
 **由Thorpe Obazee  
  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 of INNER 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 while LEFT 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 return 1 every day. We also should not use DAYOFMONTH on c.day in the GROUP BY and ORDER BY since it is already what we need.

                        
这篇关于在SQL中为每一天获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！