想获取mysql结果中的所有日期 [英] wanted to get all dates in mysql result

问题描述

我有一个名为user(id，name，join_on)的mysql表，join on是一个日期字段，我想要显示的是每天可以创建多少个用途，我可以使用group by，但是只会给出日期当用户被添加时，例如 日期

I have mysql table called user(id, name, join_on) join on is a date field what I want is to show in each day how many uses has been created I can use group by but it will only give me the dates when users get added like if date

4/12/10  5 users added
4/13/10  2 users added
4/15/10  7 users added

此处缺少日期4/14/10，我希望列出一个月内的所有日期. 我有一个解决方案，通过创建另一个仅用于添加日期的表，该表将在join_on上离开联接我的用户表，并给出总结果，但我不想像创建那样做，我需要创建和添加条目在日期表中，请提出不同的做法.

here date 4/14/10 is missing and I want listing of all dates in one month. I have one solution for it by creating another table only for adding date and that table will left join my users table on join_on and will give total result but I don't want to do that as for creating that I need to create and add entries in date table please suggest the different approach for doing so.

谢谢.

推荐答案

有一种方法可以在纯SQL中执行此操作，但有其局限性.

There is an approach that can do this in pure SQL but it has limitations.

首先，您需要将数字序列1,2,3 ... n作为行(假设select row from rows返回该数字).

First you need to have a number sequence 1,2,3...n as rows (assume select row from rows return that).

然后，您可以对此进行保留，并根据介于最小和最大之间的天数将其转换为日期.

Then you can left join on this and convert to dates based on number of days between min and max.

 select @min_join_on := (select min(join_on) from user);
 select @no_rows := (select datediff(max(join_on), @min_join_on) from user)+1;

将为您提供所需的行数，然后您可以将其用于

will give you the required number of rows, which then you can use to

 select adddate(@min_join_on, interval row day) from rows where row <= @no_rows;

将返回所需的日期序列，然后您可以在该日期序列上进行左联接回到users表.
如果您使用子查询，则可以避免使用变量，出于可读性考虑，我将其分解.

will return a required sequence of dates on which then you can do a left join back to the users table.
Using variables can be avoided if you use sub queries, I broke it down for readability.

现在，问题在于表rows中的行数必须大于@no_rows. 对于10,000行，您可以使用最长27年的日期范围，对于100,000行，您可以使用最长273年的日期范围(这确实很糟糕，但是我担心如果您不想使用存储过程，它必须看起来和感觉很尴尬).

Now, the problem is that the number of rows in table rows has to be bigger then @no_rows. For 10,000 rows you can work with date ranges of up to 27 years, with 100,000 rows you can work with date ranges of up to 273 years (this feels really bad, but I am afraid that if you don't want to use stored procedures it will have to look and feel awkward).

因此，如果您可以使用这样的固定日期范围，则甚至可以用查询替换该表，例如

So, if you can work with such fixed date ranges you can even substitute the table with the query, such as this

SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=0) r

这将产生10,000行(从1到10,000)，并且效率不会很低.

which will produce 10,000 rows going from 1 to 10,000 and it will not be terribly inefficient at it.

因此，最后它可以在单个查询中完成.

So at the end it is doable in a single query.

create table user(id INT NOT NULL AUTO_INCREMENT, name varchar(100), join_on date, PRIMARY KEY(id));

mysql> select * from user;
+----+-------+------------+
| id | name  | join_on    |
+----+-------+------------+
|  1 | user1 | 2010-04-02 | 
|  2 | user2 | 2010-04-04 | 
|  3 | user3 | 2010-04-08 | 
|  4 | user4 | 2010-04-08 | 
+----+-------+------------+
4 rows in set (0.00 sec)

insert into user values (null, 'user1', '2010-04-02'), (null, 'user2', '2010-04-04'), (null, 'user3', '2010-04-08'), (null, 'user4', '2010-04-08')


SELECT date, count(id)
FROM (
SELECT adddate((select min(join_on) from user), row-1) as date 
FROM ( 
SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (SELECT @row:=0) r ) n  
WHERE n.row <= ( select datediff(max(join_on), min(join_on)) from user) + 1
) dr LEFT JOIN user u ON dr.date = u.join_on
GROUP BY dr.date

+------------+-----------+
| date       | count(id) |
+------------+-----------+
| 2010-04-02 |         1 | 
| 2010-04-03 |         0 | 
| 2010-04-04 |         1 | 
| 2010-04-05 |         0 | 
| 2010-04-06 |         0 | 
| 2010-04-07 |         0 | 
| 2010-04-08 |         2 | 
+------------+-----------+
7 rows in set (0.00 sec)

这篇关于想获取mysql结果中的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！