SQL 连续行之间的日期差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9994862/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date Difference between consecutive rows
提问by Mohammed Rishal
I have a table with following structure
我有一个具有以下结构的表
ID Account Number Date
1 1001 10/9/2011 (dd/mm/yyyy)
2 2001 1/9/2011 (dd/mm/yyyy)
3 2001 3/9/2011 (dd/mm/yyyy)
4 1001 12/9/2011 (dd/mm/yyyy)
5 3001 18/9/2011 (dd/mm/yyyy)
6 1001 20/9/2011 (dd/mm/yyyy)
Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number The expected result would be !!
基本上我想做的是有一个访问查询来计算连续记录的日期差异但对于相同的帐号预期的结果是！！
1001 10/9/2011 - 12/9/2011 2 days
1001 12/9/2011 - 20/9/2011 8 days
1001 20/9/2011 NA
Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number , in the above example would be 1001. (the dates don't have to be shown in the result)
基本上我想做的是有一个访问查询来计算连续记录的日期差异，但对于相同的帐号，在上面的例子中是 1001。（日期不必显示在结果中）
I use access 2003.
我使用访问 2003。
回答by GarethD
SELECT T1.ID,
T1.AccountNumber,
T1.Date,
MIN(T2.Date) AS Date2,
DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM YourTable T1
LEFT JOIN YourTable T2
ON T1.AccountNumber = T2.Accountnumber
AND T2.Date > T1.Date
GROUP BY T1.ID, T1.AccountNumber, T1.Date;
or
或者
SELECT ID,
AccountNumber,
Date,
NextDate,
DATEDIFF("D", Date, NextDate)
FROM ( SELECT ID,
AccountNumber,
Date,
( SELECT MIN(Date)
FROM YourTable T2
WHERE T2.Accountnumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM YourTable T1
) AS T
回答by vikrant rana
you ca also use LAG analytical function to get the desired results as :
您还可以使用 LAG 分析函数来获得所需的结果：
Suppose below is your input table:
假设下面是您的输入表：
id account_number account_date
1 1001 9/10/2011
2 2001 9/1/2011
3 2001 9/3/2011
4 1001 9/12/2011
5 3001 9/18/2011
6 1001 9/20/2011
select id,account_number,account_date,
datediff(day,lag(account_date,1) over (partition by account_number order by account_date asc),account_date)
as day_diffrence
from yourtable;
Here is your output:
这是您的输出：
id account_number account_date day_diffrence
1 1001 9/10/2011 NULL
4 1001 9/12/2011 2
6 1001 9/20/2011 8
2 2001 9/1/2011 NULL
3 2001 9/3/2011 2
5 3001 9/18/2011 NULL
回答by Fionnuala
You can add a WHERE statement for the account number, if required. Your table is called t4
如果需要，您可以为帐号添加 WHERE 语句。你的桌子被称为t4
SELECT
t4.ID,
t4.AccountNumber,
t4.AcDate,
(SELECT TOP 1 AcDate
FROM t4 b
WHERE b.AccountNumber=t4.AccountNumber And b.AcDate>t4.AcDate
ORDER BY AcDate DESC, ID) AS NextDate,
[NextDate]-[AcDate] AS Diff
FROM t4
ORDER BY t4.AcDate;
回答by Jimmy
GarethD's answer worked for me perfectly.
GarethD 的回答对我很有效。
FYI: When you need ORDER BY clause, please use it at the end of SELECT query in the root.
仅供参考：当您需要 ORDER BY 子句时，请在根中 SELECT 查询的末尾使用它。
SELECT ConsignorID,
DateRequired StartDate,
NextDate,
DATEDIFF("D", DateRequired, NextDate)
FROM ( SELECT ConsignorID,
DateRequired,
(SELECT MIN(DateRequired)
FROM "TABLENAME" T2
WHERE T2.DateRequired > T1.DateRequired
) AS NextDate
FROM "TABLENAME" T1
) AS T
ORDER BY T.DateRequired ASC
按 T.Date 订购需要 ASC
回答by Diego
try this:
尝试这个：
select [Account Number], DATEDIFF(DD, min(date), max(date)) as dif
from your_table
group by [Account Number]
回答by school student
SELECT ID,
AccountNumber,
Date,
NextDate,
DATEDIFF("D", Date, NextDate)
FROM ( SELECT ID,
AccountNumber,
Date,
( SELECT MIN(Date)
FROM YourTable T2
WHERE T2.Accountnumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM YourTable T1
) AS T
