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

问题描述

SELECT User_JoinDate,COUNT(User_ID)从用户那里`User_JoinDate`在2012-11-22"和2012-12-06"之间GROUP BY User_JoinDateORDER BY User_JoinDate ASC"

我正在生成要在折线图中显示的数据.不幸的是，我无法弄清楚如何让上述查询在没有用户注册的日期显示 0.所以目前我的输出可能是这样的:

<前>2012-11-22 - 22012-11-25 - 4

但我想要的是

<前>2012-11-22 - 22012-11-23 - 02012-11-24 - 02012-11-25 - 4

我目前有一个工作版本，它将 MySQL 结果存储到一个数组中，然后 PHP 循环并填充空白.这看起来很混乱，我希望有一个通过 MySQL 的解决方案.我对该网站进行了很好的搜索，但很难理解其中的一些实现.有什么建议?

解决方案

您可以使用 MySQL 变量为您想要的所有日期构建自动结果集.

选择AllDaysYouWant.MyJoinDate，count( U.User_ID ) as NumberJoined从( 选择@curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate从(选择@curDate := '2012-11-21')sqlvars，用户限制 18 ) AllDaysYouWant左加入用户U在 AllDaysYouWant.MyJoinDate = U.User_JoinDate通过...分组AllDaysYouWant.MyJoinDate

内部查询，我只是在没有键的情况下加入用户表，所以它只是用来循环 X 条记录来表示你想要的日期跨度......这可能是 30、100，无论如何........ 只要表(在本例中为用户)具有与您期望的一样多的记录.

然后，除了几天之外什么都没有的结果加入到用户表中，但这一次，基于用户的 JOIN_DATE.简单的 COUNT() 应该可以满足您的需求.

AllDaysYouWant"是分配给

内部第一部分查询的别名

 ( 选择@curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate从(选择@curDate := '2012-11-21')sqlvars，用户限制 18 ) AllDaysYouWant

这基本上说明...从用户表(但可以是任何)，给我 18 行数据(通过限制，但几乎可以是任意数量的记录，但您只需要从 11 月 22 日到 12 月 6 日，这只有 14 天，但我做了 18 天只是为了原则它几乎可以是任何东西.用户表上方是 (select @curDate := '2012-11-21') sqlvars.查询中包含的任何选择语句作为表源的括号必须有一个别名，因为它只是我将使用的一个变量，所以不要关心它的名字是什么.所以，这个查询从 11 月 21 日开始变量和 Select @curDate := Date_Add...blah blah 声明要获取@curDate 的当前值，为其添加 1 天(现在变为 11 月 22 日)并将其存储在返回的行MyJoinDate"中.所以现在，这个内部查询创建了您的日期表从 11 月 22 日开始向前 18 天的数据，并具有别名AllDaysYouWant"供查询的其余部分参考.

我已将您可能遇到的查询调整为 alias.field 以供澄清...

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 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

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?

解决方案

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

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.

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.

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

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.

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

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