显示之间的所有日期，即使没有结果 [英] 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"

I' m生成的数据要显示在一个线形图中，不幸的是我无法弄清楚如何让上面的查询在没有用户注册的日期显示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

但我想要的是

But what I want is

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?

推荐答案

您可以使用MySQL变量创建一个自动的结果集，用于您想要的所有日期。 b

You can build out an automated result set using MySQL variables for all the dates you want.

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

这基本上表明...从用户表（但可以是任何），给我18行数据（通过限制，但可能几乎任何数量的记录，但你只需要从11月22日至12月6日，这只是14天，但我做了18只是为了原则，它几乎可以做任何事情。Users表之上是（select @curDate：='2012-11-21'）sqlvars。被包装的查询中的任何select语句在括号中作为表格源必须被赋予一个别名，因为它只是一个我将要使用的变量，不关心它的名字是什么，所以，这个查询启动了变量le在11月21日和选择@curDate：=日期_添加...等等等等说明采取@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...

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