当前位置： 文江博客话题详情

SQL - 两个日期之间的查询范围（非 VBA）

发布于 2024-08-28 11:58:52 字数 732 浏览 9 评论 0原文

我看到了有关堆栈溢出的各种主题，但没有一个适合 MS-Access 的内容...

给定开始日期和结束日期，是否有办法通过 SQL 返回时间范围内每个给定月份的记录？

EG：

A record has a Start Date of #1/1/2010# and an End Date of #1/31/2010#

Running the query against that single record, I would like the results to be
            #1/4/2010#   (Monday the 4th)
            #1/11/2010#  (Monday the 11th)
            #1/18/2010#   ...etc
            #1/25/2010#

限制

  • MS-Access 2003：SQL 内无 Case/Loops（IIF 语句很好）
  • 这只是一个视图，不会使用 VBA，因为数据不会被篡改。断开连接的记录集是我的最后一个选择。我更愿意找出某种方法来调用 SQL 中的自定义函数来帮助返回这些值...当您迭代此日期范围时，某些类存储在全局范围内...

这可能吗？我看到很多不，但如果有一种方法可以将值传递到函数中，我可以找到一种方法来完成这项工作。遗憾的是我没有办法在不使用 ad/c 记录集的情况下模拟存储过程，至少我知道......那里的专家知道一种方法吗？

原文

I see various topics on this around stack overflow but none that fit the contect of MS-Access...

Given a starting date and an ending date, is there a way through SQL to return records for each given month within the time frame?

EG:

A record has a Start Date of #1/1/2010# and an End Date of #1/31/2010#

Running the query against that single record, I would like the results to be
            #1/4/2010#   (Monday the 4th)
            #1/11/2010#  (Monday the 11th)
            #1/18/2010#   ...etc
            #1/25/2010#

Restrictions

  • MS-Access 2003 :No Case/Loops inside the SQL (IIF statements are good)
  • This is a view only, NO VBA will be used since the data will not be tampered with. Disconnected recordset is my last option. I would prefer to find out theres some way to call your customized functions in the SQL to help return these values... some class stored on a global scope while you iterate through this date range maybe...

Is this possible? I see many no's, but if there was a way to pass a value into a function I could find a way to make this work. Sad that I don't have a way to simulate a stored procedure without using a d/c recordset, at least that I know of... any experts out there know a way?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

万劫不复 2024-09-04 11:58:54

由于这可能与报告有关，您可以使用临时表方法，这还不错，特别是如果您可能在给定时间段内运行多个报告（但要注意多个用户）。

现在，只是为了好玩，这里有一种超级丑陋但半灵活的方法（使用有 10 个条目的表，工作范围为 273 年）

创建一个名为 t10 的表，其中包含一个字段 - id（让它很长并且是主键）并输入十行：0,1,2,3..9

之后

SELECT #1/1/2010#+
       [t10].[ID]+
       [t100].[iD]*10+
       [t1000].[ID]*100+
       [t10000].[ID]*1000+
       [t100000].[iD]*10000 AS Mondays,
FROM t10, 
     t10 AS t100, 
     t10 AS t1000, 
     t10 AS t10000, 
     t10 AS t100000
WHERE  Weekday(#1/1/2010#+
               [t10].[ID]+
               [t100].[iD]*10+
               [t1000].[ID]*100+
               [t10000].[ID]*1000+  
               [t100000].[iD]*10000)=2;

返回从 2010-1-1 到 2283-10-15 的所有星期一，并且速度并不慢，因为它很难看。
当然，要获得问题的解决方案，您必须过滤字段 date1 和 date2 的结果。

这本质上是与临时表相同的解决方案，主要区别在于您不必在每个查询上重建表。

您可以仅使用一个条目为 0..99999 的表来实现相同的结果，如果我要使用临时表，我可能会使用类似的东西。

Since this is probably related to reporting you could use temporary table approach, that is not so bad, especially if you might be running multiple reports against a given period (but do take care about multiple users).

Now, just for fun, here's a super-ugly, but semi-flexible approach (uses table with 10 entries and works for a range of 273 years)

Make a table called t10 with one field - id (make it long and primary key) and enter ten rows: 0,1,2,3..9

After that

SELECT #1/1/2010#+
       [t10].[ID]+
       [t100].[iD]*10+
       [t1000].[ID]*100+
       [t10000].[ID]*1000+
       [t100000].[iD]*10000 AS Mondays,
FROM t10, 
     t10 AS t100, 
     t10 AS t1000, 
     t10 AS t10000, 
     t10 AS t100000
WHERE  Weekday(#1/1/2010#+
               [t10].[ID]+
               [t100].[iD]*10+
               [t1000].[ID]*100+
               [t10000].[ID]*1000+  
               [t100000].[iD]*10000)=2;

returns all Mondays from 2010-1-1 to 2283-10-15 and is not as slow as it is ugly.
Of course to get a solution for your question you'll have to filter the results for your fields date1 and date2.

This is in essence same solution as having temporary table, with the main difference that you don't have to rebuild the table on each query.

You could achieve the same result with only one table with entries 0..99999, and if i was to use temp tables I would probably use something like that.

回复 原文
铁轨上的流浪者 2024-09-04 11:58:54

您正在讨论从单个记录中生成多个记录（每个日期一个）的查询。我想这应该通过存储过程（如果您的数据库是 SQL）或通过记录集来完成。任何查询都无法解决问题。创建记录集并在需要时添加记录对我来说似乎非常简单。你应该去争取。

You are talking about a query that generates multiple records (one per date) out of a single record. I guess this should be made through a stored procedure (if your database is SQL) or through recordsets. No query will do the trick. Creating a recordset and adding records where needed seems quite straightforward to me. You should go for it.

回复 原文
执着的年纪 2024-09-04 11:58:54

我希望我正确理解你的问题。为此，我们需要创建一个笛卡尔积 - 因此您应该创建另一个表。它只需要一个 ID，其中包含从 1 到日期范围将包含的最大周数的数字。

For example table: tCounter(CounterID int)
CounterID
1
2
3
4
etc..

对于测试数据，我使用您的数据创建了一个模拟表

For example: tDates(DateID int, startdate datetime, enddate datetime)
DateID, Startdate, EndDate
1,  1/1/10      1/31/10
2,  1/18/10     3/4/10
3,  2/1/10      2/28/10

，将生成结果的 msaccess sql 语句如下所示：

SELECT tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7) AS NewDay
FROM tDates, tCounter
WHERE (((((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7))<=[enddate]))
ORDER BY tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7);

如果您想将日期从星期一更改为另一天，请更改工作日函数中的参数。 1=星期日->7=星期六

e.g. Weekday([startdate],3)...the 3=Tuesday so result is Monday 
e.g. Weekday([startdate],5)...the 5=Thursday so result is Wednesday

希望对您有用。

I hope I understand your question correctly. To to this we need to create a cartesian product - so you should create another table. It only needs an ID in it with numbers that go from 1 to the highest number of weeks that your date range will contain.

For example table: tCounter(CounterID int)
CounterID
1
2
3
4
etc..

for test data I created a mock table with your data

For example: tDates(DateID int, startdate datetime, enddate datetime)
DateID, Startdate, EndDate
1,  1/1/10      1/31/10
2,  1/18/10     3/4/10
3,  2/1/10      2/28/10

the msaccess sql statement that will produce your results looks like

SELECT tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7) AS NewDay
FROM tDates, tCounter
WHERE (((((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7))<=[enddate]))
ORDER BY tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7);

If you want to change the day from Monday to another day, change the argument in the weekday function. 1=Sunday->7=Saturday

e.g. Weekday([startdate],3)...the 3=Tuesday so result is Monday 
e.g. Weekday([startdate],5)...the 5=Thursday so result is Wednesday

Hope that works for you.

回复 原文
芯好空 2024-09-04 11:58:53

您可以创建一个巨大的日历表，其中包含日期和天数。 2010 年 4 月 12 日，星期一； 2010 年 4 月 13 日、星期二等。然后查询该巨型表以获取结果。过去和未来需要多大的范围？尽管这是一个笨拙的解决方案，但它肯定会使查询变得干净简单。

You could create a giant calendar table, with dates and days in it. 4/12/10, Monday; 4/13/10, Tuesday, etc. Then query that giant table for the results. How big of range in the past and future do you need? Even though this is a clumsy solution, it would certainly make the queries clean and simple.

回复 原文
~没有更多了~

关于作者

錯遇了你

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

胡图图

文章 0 评论 0

zt006

文章 0 评论 0

z祗昰~

文章 0 评论 0

冰葑

文章 0 评论 0

野の

文章 0 评论 0

天空

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文