oracle Sql查询以查找多行之间的日期段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5489054/
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
Sql query to find date period between multiple rows
提问by nthed
I have a table with three columns (City_Code | Start_Date | End_Date).
Suppose i have the following data:
我有一个包含三列的表（City_Code | Start_Date | End_Date）。
假设我有以下数据：
New_York|01/01/1985|01/01/1987
Paris|02/01/1987|01/01/1990
San Francisco|02/01/1990|04/01/1990
Paris|05/01/1990|08/01/1990
New_York|09/01/1990|11/01/1990
New_York|12/01/1990|19/01/1990
New_York|20/01/1990|28/01/1990
I would like to get the date period for which someone lived in the last city of his residence. In this example that is New_York(09/01/1990-28/01/1990) using only sql. I can get this period by manipulating the data with java , but is it possible to do it with plain sql?
我想获取某人在其居住地的最后一个城市居住的日期。在此示例中，New_York(09/01/1990-28/01/1990) 仅使用 sql。我可以通过用 java 操作数据来获得这段时间，但是可以用普通的 sql 来做到这一点吗？
Thanks in advance
提前致谢
回答by breed052
You can grab the first and last date of residence by city using this:
您可以使用以下方法按城市获取居住的第一个和最后一个日期：
SELECT TOP 1 City_Code, MIN(Start_Date), Max(End_Date)
FROM Table
GROUP BY City_Code
ORDER BY Max(End_Date) desc
but, the problem is that the start date will be the first date of residence in the city in question.
但是，问题是开始日期将是在相关城市居住的第一个日期。
回答by breed052
For 10g you don't have the option of SELECT TOP n so you must be a little creative.
对于 10g，您没有 SELECT TOP n 选项，因此您必须有点创意。
WITH last_period
AS
(SELECT city, moved_in, moved_out, NVL(moved_in-LEAD(moved_out, 1) OVER (ORDER BY city), 0) AS lead
FROM periods
WHERE city = (SELECT city FROM periods WHERE moved_out = (SELECT MAX(moved_out) FROM periods)))
SELECT city, MIN(moved_in) AS moved_in, MAX(moved_out) AS moved_out
FROM last_period
WHERE lead >= 0
GROUP BY city;
This works for the example dataset that you have given. It could stand some optimisation for a large dataset but gives you a working example, tested on Oracle 10g.
这适用于您提供的示例数据集。它可以对大型数据集进行一些优化，但为您提供了一个在 Oracle 10g 上测试的工作示例。
回答by Randy
i'm short on time - but this feels like you could use the window function LAGto compare to the previous row and retain the appropriate begin date from that row when the city changes, and dont change it when the city is the same - this should correctly preserve the range.
我时间不够 - 但这感觉就像您可以使用窗口函数LAG与前一行进行比较，并在城市更改时保留该行的适当开始日期，并且在城市相同时不要更改它 - 这应该正确保留范围。
回答by Thomas
Given that this is Oracle, you can simply subtract the end date and start date to get the number of days in between.
鉴于这是 Oracle，您可以简单地减去结束日期和开始日期以获得两者之间的天数。
Select City_Code, (End_Date - Start_Date) Days
From MyTable
Where Start_Date = (
Select Max( T1.Start_ Date )
From MyTable As T1
)
回答by aman.nepid
SELECT
MAX(t.duration)
FROM (
SELECT
(End_Date - Start_Date) duration
From
Table
) as t
I hope this will work.
我希望这会奏效。
回答by Andriy M
If you want to calculate only the last period length for the last city of residence, then it's probably something like this:
如果你只想计算最后一个居住城市的最后一个时期的长度，那么它可能是这样的：
SELECT TOP 1
City_Code,
End_Date - Start_Date AS Days
FROM atable
ORDER BY Start_Date DESC
But if you mean to include all the periods the person has ever lived in a city that happens to be their last city of residence, then it's a bit more complicated, but not too much:
但是，如果您的意思是包括此人曾经在恰好是他们最后居住城市的城市中居住过的所有时期，那么情况会稍微复杂一些，但不会太多：
SELECT TOP 1
City_Code,
SUM(End_Date - Start_Date) AS Days
FROM atable
GROUP BY City_Code
ORDER BY MAX(Start_Date) DESC
But the above solution most probably returns the last city information only after it calculates the data for all cities. Do we need that? Not necessarily, so maybe we should use another approach. Maybe like this:
但是上面的解决方案很可能只有在计算了所有城市的数据后才会返回最后一个城市的信息。我们需要那个吗？不一定，所以也许我们应该使用另一种方法。也许是这样的：
SELECT
City_Code,
SUM(End_Date - Start_Date) AS Days
FROM atable
WHERE City_Code = (SELECT TOP 1 City_Code FROM atable ORDER BY Start_Date DESC)
GROUP BY City_Code
回答by Andriy M
If it's MySQL, you can easily use
如果是MySQL，你可以轻松使用
TIME_TO_SEC(TIMEDIFF(end_date, start_date)) AS `diff_in_secs`
Having time difference in seconds you go any further.
有以秒为单位的时差，你可以走得更远。
回答by tom502
If you are using SQL Server you can use the DateDiff() function
如果您使用的是 SQL Server，则可以使用 DateDiff() 函数
DATEDIFF ( datepart , startdate , enddate )
http://msdn.microsoft.com/en-us/library/ms189794.aspx
http://msdn.microsoft.com/en-us/library/ms189794.aspx
EDIT
编辑
I don't know Oracle but I did find this article
我不知道 Oracle 但我确实找到了这篇文章
回答by Bsand
On SQL Server, couldn't you use:
在 SQL Server 上，您不能使用：
SELECT TOP 1 City_Code, Start_Date + "-" + End_Date
FROM MyTable
ORDER BY enddate DESC
That would get the date period and city with the latest end date. This is assuming you are trying to just find the city where the person most recently lived, formatted with a dash.
这将获得具有最新结束日期的日期时间段和城市。这是假设您正在尝试查找此人最近居住的城市，格式为破折号。
