MySQL显示所有日期之间，即使没有结果 [英] MySQL show all dates between, even if no result

问题描述

SELECT User_JoinDate, 
COUNT(User_ID) 
FROM Users WHERE `User_JoinDate` 
BETWEEN '2012-11-22' AND '2012-12-06' 
GROUP BY User_JoinDate 
ORDER BY User_JoinDate ASC"

不幸的是我不知道如何获取上面的查询显示0为没有用户可能已经注册的日期。所以目前我的输出可能是这样的：

I'm generating data to be displayed in a line graph. Unfortunately I can't figure out how to get the above query to display 0 for a date that no users might have registered. So currently my output might be something like this:

2012-11-22 - 2
2012-11-25 - 4

但我想要的是

2012-11-22 - 2
2012-11-23 - 0
2012-11-24 - 0
2012-11-25 - 4

目前有一个工作版本，将MySQL结果存储到一个数组，然后PHP循环并填充空白这似乎很麻烦，我希望有一个解决方案通过MySQL。我有一个很好的搜索网站，但苦苦了解一些实现。有什么建议么？

I do currently have a working version which stores the MySQL result into an array and then PHP loops through and fills the blanks. This seems very messy and I was hoping there would be a solution through MySQL. I have had a good search of the site but struggling to understand some of the implementations. Any suggestions?

谢谢。

推荐答案

select
      AllDaysYouWant.MyJoinDate,
      count( U.User_ID ) as NumberJoined
   from
      ( select
              @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
           from
              ( select @curDate := '2012-11-21' ) sqlvars,
              Users
           limit 18 ) AllDaysYouWant
      LEFT JOIN Users U
         on AllDaysYouWant.MyJoinDate = U.User_JoinDate
   group by
      AllDaysYouWant.MyJoinDate

内部查询，我刚刚加入users表，键，所以它只是用来循环通过X数量的记录来表示你想要的日跨度...这可以是30，100，无论什么....只是一个表（在这种情况下，用户），有

The inner query, I am just joining to the users table with no key, so its just used to cycle through X number of records to represent the day span you want... This could be 30, 100, whatever.... Just as long a the table (in this case users), has as many records as you are expecting.

那么，除了天之外的结果被加入users表，但是这次基于用户的JOIN_DATE。

THEN, that result of nothing but days is joined to the users table, but this time, based on the JOIN_DATE of the user. The simple COUNT() should get you what you want.

AllDaysYouWant是分配给

The "AllDaysYouWant" is the alias assigned to the internal first part query of

  ( select
          @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
       from
          ( select @curDate := '2012-11-21' ) sqlvars,
          Users
       limit 18 ) AllDaysYouWant

$ b b

这基本上表示...从users表（但可以是任何），给我18行数据（通过limit，但可能几乎任何数量的记录，但你只需要从11月22日到12月6日，这只是14天，但我只是为了原则它可能几乎任何东西。上面的用户表是（选择@curDate：='2012-11-21'）sqlvars。任何select语句在括号中作为表源必须给出一个别名，因为它只是一个变量，我将使用，不管它的名字是什么，所以，这个查询在11月21日启动变量，Select @curDate： Date_Add ... blah blah表示取当前值@curDate，向它添加1天（现在变为11月22日），并将其存储在返回的行MyJoinDate中。所以现在，这个内部查询创建你的表，从11月22日前18天的数据的值，并有别名AllDaysYouWant的查询余下的参考。

This basically states... From the users table (but could be any), give me 18 rows of data (via limit, but could be almost any number of records, but you only need from Nov 22 to Dec 6, which is only 14 days, but I did 18 just for principle it could be almost anything. Above the Users table is (select @curDate := '2012-11-21' ) sqlvars. Any select statement within a query that is wrapped in parentheses as a table source must be given an alias and since it is just a variable I'll be using, don't care what its name is. So, this query starts the variable at Nov 21 and the Select @curDate := Date_Add...blah blah states to take the current value of @curDate, add 1 day to it (now becomes Nov 22) and store it in the returned row "MyJoinDate". So now, this inner query creates your table of just dates going from Nov 22 forward 18 days worth of data and has the alias "AllDaysYouWant" for the rest of the query to reference.

我已经调整了查询​​，可能是您遇到的，alias.field一切澄清...

I've adjusted the query which was probably what you encountered, to alias.field everything for clarification...

这篇关于MySQL显示所有日期之间，即使没有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！