SQL 查询开始和结束日期

发布于 2024-12-02 21:14:47 字数 948 浏览 1 评论 0原文

而且我不知道如何表达这个问题，因为我的英语很差。

表

Name | Date
Foo | 1-Sep
Foo | 2-Sep
Foo | 3-Sep
Foo | 5-Sep
Foo | 6-Sep
Foo | 7-Sep

Foo | 1-Dec
Foo | 2-Dec

Foo | 4-Dec
Foo | 5-Dec
Foo | 6-Dec
Foo | 7-Dec
Foo | 8-Dec

Foo | 1-Feb

Foo | 14-Feb
Foo | 15-Feb
Foo | 16-Feb
Foo | 17-Feb
Foo | 18-Feb
Foo | 19-Feb
Foo | 20-Feb

Foo | 22-Feb

我需要创建一个具有以下结果的查询

查询结果

Name | Start Date  |  End Date
Foo  |  1-Sep      | 3-Sep
Foo  |  5-Sep      | 7-Sep
Foo  |  4-Dec      | 8-Dec
Foo  |  1-Feb      | 1-Feb
Foo  |  1-Dec      | 2-Dec
Foo  |  14-Feb     | 20-Feb
Foo  |  22-Feb     | 22-Feb

更新：

开始日期：一系列连续日期的第一天
结束日期：一系列连续日期的最后一天天
如果日期不包含在一系列连续的天中，例如上面的 2 月 22 日，则它是开始日期和结束日期。

原文

This is slightly different than this: SQL Query Creating Start and End Dates

And I do not know how to phrase this question as I suck in English.

Table

Name | Date
Foo | 1-Sep
Foo | 2-Sep
Foo | 3-Sep
Foo | 5-Sep
Foo | 6-Sep
Foo | 7-Sep

Foo | 1-Dec
Foo | 2-Dec

Foo | 4-Dec
Foo | 5-Dec
Foo | 6-Dec
Foo | 7-Dec
Foo | 8-Dec

Foo | 1-Feb

Foo | 14-Feb
Foo | 15-Feb
Foo | 16-Feb
Foo | 17-Feb
Foo | 18-Feb
Foo | 19-Feb
Foo | 20-Feb

Foo | 22-Feb

I need to create a query with the below result

Query Result

Name | Start Date  |  End Date
Foo  |  1-Sep      | 3-Sep
Foo  |  5-Sep      | 7-Sep
Foo  |  4-Dec      | 8-Dec
Foo  |  1-Feb      | 1-Feb
Foo  |  1-Dec      | 2-Dec
Foo  |  14-Feb     | 20-Feb
Foo  |  22-Feb     | 22-Feb

Update:

Start Date: First day of a series of consecutive days
End Date: Last day of a series of consecutive days
If a date is not included in a series of consecutive days, like Feb 22 above, it is the start and end date.

分享到QQ

分享到微博

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

发布评论

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

久而酒知 2024-12-09 21:14:47

不确定这是否是最好的方法。您可以首先过滤具有前一天和后一天的所有日期，然后可以使用此处

过滤不重要日期的查询：

select t1.Name, t.Date from Table t1
left outer join Table t2 on dateadd(d,-1,t1.Date) = t2.Date 
left outer join Table t3 on dateadd(d,1,t1.Date) = t3.Date
where t2.Date is null or t3.Date is null

Not sure if its the best way. You can first filter all dates that have previous and next day, then you can use solution explained in here

Query that filters unimportant dates:

select t1.Name, t.Date from Table t1
left outer join Table t2 on dateadd(d,-1,t1.Date) = t2.Date 
left outer join Table t3 on dateadd(d,1,t1.Date) = t3.Date
where t2.Date is null or t3.Date is null

回复收藏 0 原文

~没有更多了~