如何在SQL中找到一些日期 [英] How do I find some dates in SQL
问题描述
Hello All
我在SQL Server数据库的一列中有一堆日期，看起来像这样
Hello All
I have a bunch of dates in a column in an SQL Server DB that looks like this
2014-01-29
2014-01-30
2014-02-03
2014-02-04
2014-02-05
2014-02-06
2014-02-11
2014-02-12
2014-02-13
2014-02-17
2014-02-18
2014-02-19
2014-02-20
2014-02-25
2014-02-26
2014-02-27
2014-03-03
2014-03-04
2014-03-05
2014-03-06
2014-03-11
2014-03-12
2014-03-13
我对SQL有几个问题。
1.我想知道如果最大日期是2014-03-13我怎么能找到2014-03-11？基本上我试图找到日期休息后的第一天。
2.接下来我正在尝试计算此列表中未显示的日期
3.最后，有没有办法在失踪日找到某个日期。 2014-03-13中的示例找到未显示的第6个缺失日。 （应该是'2014-03-01'）
我有更多问题，但我很确定我会从中解决这个问题。 />
谢谢
Mike
There's a couple of questions that I have with SQL.
1. I'm trying to figure out how I can find 2014-03-11 if max day is 2014-03-13? Basically I'm trying to find the first day after there's been a break in dates.
2. Next I'm trying to do a count of the days that are NOT shown in this list
3. Lastly, is there a way to find a certain date in missing days. Example from '2014-03-13' find the 6th missing day that is not shown. (It should '2014-03-01')
I've got more questions but I'm pretty sure I'll figure it out from this.
Thanks
Mike
推荐答案
这是一个非常有趣的经典SQL问题，称为孤岛（google it），其中包含一大堆不断增长的解决方案。
我会指向一篇具体而优秀的文章 [ ^ ]这应该让你开始。当你遇到更具体的问题时，你可以回到这里
This is a quite intriguing classic sql problem called "islands and gaps" (google it) with a whole load of solutions that keep growing.
I'll point you to one specific and good article[^] that should get you started. And you can come back here when you have more specific problems
这不是你所有问题的完整答案，因为用通用语言来完成这些问题要简单得多。你真的需要用SQL语言回答吗？
我们假设如此。
有一些方法可以生成日期范围。因此，您可以将范围与您必须找到差距的日期进行匹配。请参阅： http：//consultingblogs.emc。 com / jamiethomson / archive / 2007/01/11 / T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx [ ^ ]。
This is not a complete answer to all your questions, because it is much simpler to do these in a general purpose language. Do you really need to answer in SQL-only?
Let's suppose so.
There are methods to generate a date range. So you can match the range with the dates you have to find the gaps. See: http://consultingblogs.emc.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx[^].
Not确定你想要什么，但看看例子：
Not sure what you want to achive, but have a look at example:
DECLARE @tmp TABLE (MyDate DATETIME)
INSERT INTO @tmp (MyDate)
VALUES ('2014-01-29'),
('2014-01-30'), ('2014-02-03'),
('2014-02-04'), ('2014-02-05'),
('2014-02-06'), ('2014-02-11'),
('2014-02-12'), ('2014-02-13'),
('2014-02-17'), ('2014-02-18'),
('2014-02-19'), ('2014-02-20'),
('2014-02-25'), ('2014-02-26'),
('2014-02-27'), ('2014-03-03'),
('2014-03-04'), ('2014-03-05'),
('2014-03-06'), ('2014-03-11'),
('2014-03-12'), ('2014-03-13')
SELECT t1.MyDate AS FirstDate, t2.MyDate AS SecondDate, DATEDIFF(d, t1.MyDate, t2.MyDate) AS DayDiff
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY MyDate) AS RowNo, MyDate
FROM @tmp
)
AS t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY MyDate) AS RowNo, MyDate
FROM @tmp
) AS t2
ON t1.RowNo = t2.RowNo-1
结果：
Result:
FirstDate SecondDate DayDiff
2014-01-29 00:00:00.000 2014-01-30 00:00:00.000 1
2014-01-30 00:00:00.000 2014-02-03 00:00:00.000 4
2014-02-03 00:00:00.000 2014-02-04 00:00:00.000 1
2014-02-04 00:00:00.000 2014-02-05 00:00:00.000 1
2014-02-05 00:00:00.000 2014-02-06 00:00:00.000 1
2014-02-06 00:00:00.000 2014-02-11 00:00:00.000 5
2014-02-11 00:00:00.000 2014-02-12 00:00:00.000 1
2014-02-12 00:00:00.000 2014-02-13 00:00:00.000 1
2014-02-13 00:00:00.000 2014-02-17 00:00:00.000 4
2014-02-17 00:00:00.000 2014-02-18 00:00:00.000 1
2014-02-18 00:00:00.000 2014-02-19 00:00:00.000 1
2014-02-19 00:00:00.000 2014-02-20 00:00:00.000 1
2014-02-20 00:00:00.000 2014-02-25 00:00:00.000 5
2014-02-25 00:00:00.000 2014-02-26 00:00:00.000 1
2014-02-26 00:00:00.000 2014-02-27 00:00:00.000 1
2014-02-27 00:00:00.000 2014-03-03 00:00:00.000 4
2014-03-03 00:00:00.000 2014-03-04 00:00:00.000 1
2014-03-04 00:00:00.000 2014-03-05 00:00:00.000 1
2014-03-05 00:00:00.000 2014-03-06 00:00:00.000 1
2014-03-06 00:00:00.000 2014-03-11 00:00:00.000 5
2014-03-11 00:00:00.000 2014-03-12 00:00:00.000 1
2014-03-12 00:00:00.000 2014-03-13 00:00:00.000 1
如您所见，您可以使用加入来比较日期。有关详细信息，请参阅： SQL联接的可视化表示 [ ^ ]。
使用 WHERE条件 [ ^ ]，您可以过滤数据以显示它们之间的距离大于的日期1天：
As you can see, you can compare dates by using Join. For further information, please see: Visual Representation of SQL Joins[^].
Using WHERE condition[^], you're able to filter your data to show dates where the distance between them is larger than 1 day:
WHERE DATEDIFF(d, t1.MyDate, t2.MyDate)>1
试试！
Try!
这篇关于如何在SQL中找到一些日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
