使用标准的mysql函数有一种方法来编写一个查询,该查询将返回两个日期之间的天数列表 .
例如,给定2009-01-01和2009-01-13,它将返回一个包含值的列表:
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10
2009-01-11
2009-01-12
2009-01-13
编辑:看来我还不清楚 . 我想生成这个列表 . 我有值存储在数据库中(按日期时间),但是希望它们在左外连接上聚合到上面的日期列表中(我希望在某些连接的右侧有一段时间为null并且会处理这个) .
19 回答
通常情况下,人们会使用您通常保留的辅助数字表来实现此目的,并对此进行一些修改:
我已经看到了具有表值函数等的变体 .
您还可以保留一份永久的日期列表 . 我们在数据仓库中有这个以及一天中的时间列表 .
那么如何在SQL Server中找到两个给定日期之间的日期解释http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html
我将使用此存储过程在名为 time_intervals 的临时表中生成所需的时间间隔,然后使用temp time_intervals 表JOIN并聚合数据表 .
该过程可以生成您在其中指定的所有不同类型的间隔:
类似的示例数据场景位于this post的底部,我在其中为SQL Server构建了类似的功能 .
对于MSSQL,您可以使用它 . 这非常快 .
您可以将它包装在表值函数或存储过程中,并在开始日期和结束日期作为变量进行解析 .
我们在BIRT报告中遇到了类似的问题,因为我们想报告那些没有数据的日子 . 由于这些日期没有条目,对我们来说最简单的解决方案是创建一个存储所有日期的简单表,并使用它来获取范围或连接以获得该日期的零值 .
我们每个月都有一份工作,以确保该表在未来5年内填充 . 因此创建表:
毫无疑问,使用不同的DBMS有一些神奇的棘手方法,但我们总是选择最简单的解决方案 . 该表的存储要求很小,它使查询更加简单和便携 . 从性能的角度来看,这种解决方案几乎总是更好,因为它不需要对数据进行每行计算 .
另一个选项(我们以前使用过它)是为了确保每个日期在表格中都有一个条目 . 我们定期清扫表格,并为不存在的日期和/或时间添加零条目 . 在您的情况下,这可能不是一个选项,它取决于存储的数据 .
如果您认为保持填充
all_dates
表是一件麻烦事,那么存储过程就是返回包含这些日期的数据集的方法 . 这几乎肯定会变慢,因为每次调用时都必须计算范围,而不是仅仅从表中提取预先计算的数据 .但是,说实话,你可以填写表1000年没有任何严重的数据存储问题 - 365,000个16字节(例如)日期加上一个索引重复日期加上20%的安全开销,我粗略估计约14M [365,000 * 16 * 2 * 1.2 = 14,016,000字节]),这是事物计划中的一个小表 .
您可以像这样使用MySQL的user variables:
@num为-1,因为您第一次使用它时会添加它 . 此外,您不能使用“HAVING date_sequence”,因为这会使用户变量每行增加两次 .
从this回答借用一个想法,您可以设置一个0到9的表格,并使用它来生成您的日期列表 .
这将允许您生成最多1000个日期的列表 . 如果需要更大,可以在内部查询中添加另一个交叉连接 .
对于Access(或任何SQL语言)
创建一个包含2个字段的表,我们将此表称为
tempRunDates
:字段
fromDate
和toDate
然后只插入1条记录,其中包含开始日期和结束日期 .
创建另一个表:
Time_Day_Ref
在此表中导入日期列表(将Excel中的列表很容易) .
我的案例中的字段名称是
Greg_Dt
,格里高利日期我从2009年1月1日到2020年1月1日列出了我的名单 .
运行查询:
简单!
我们在人力资源管理系统中使用了这个,你会发现它很有用
此解决方案正在与MySQL一起使用5
创建一个表 -
mytable
.架构不重要 . 重要的是它中的行数 .
因此,您可以只保留一行INT类型的10行,值为1到10 .
SQL:
限制:上述查询返回的最大日期数为
(rows in mytable)*(rows in mytable) = 10*10 = 100.
您可以通过更改sql中的表单部分来增加此范围:
来自mytable x,mytable y,mytable z
所以,范围是
10*10*10 =1000
等等 .创建一个存储过程,它接受两个参数a_begin和a_end . 在其中创建一个名为t的临时表,声明一个变量d,将a_begin指定给d,并在t中运行
WHILE
循环,并调用ADDDATE
函数来递增值d . 最后SELECT * FROM t
.我会使用类似的东西:
然后
@HOLDER
变量表保存这两个日期之间按日递增的所有日期,随时准备加入您的心灵内容 .我已经和它斗争了很长一段时间 . 由于这是我搜索解决方案时谷歌的第一次打击,让我发布到目前为止我已经到达的位置 .
将
[yourTable]
替换为数据库中的表 . 诀窍是您选择的表中的行数必须> =您想要返回的日期数 . 我尝试使用表占位符DUAL,但它只返回一行 .在这里,首先添加一天到当前的endDate,它将是2011-02-28 00:00:00,然后你减去一秒使得结束日期2011-02-27 23:59:59 . 通过这样做,您可以获得给定间隔之间的所有日期 .
输出:
2011/02/25
2011/02/26
2011/02/27
这个程序将插入从年初到现在的所有日期,只是替换“开始”和“结束”的日子,你准备好了!
我需要一份包含2个日期之间所有月份的统计数据列表 . 这两个日期是订阅的开始和结束日期 . So the list shows all months and the amount of subscriptions per month.
MYSQL
我正在使用 Server version: 5.7.11-log MySQL Community Server (GPL)
现在我们将以一种简单的方式解决这个问题 .
我创建了一个名为 "datetable" 的表
现在,我们将看到插入的记录 .
这里我们的查询是在两个日期而不是那些日期内获取记录 .
希望这会对很多人有所帮助 .
在MariaDB> = 10.3和MySQL> = 8.0中使用新的递归(公用表表达式)功能的优雅解决方案 .
以上内容返回“2019-01-01”和“2019-04-30”之间的日期表 .