英文:
SQL time difference between two dates multiple conditions
问题
ID 日期 日期2 N天 事件
1 01/01/2010 06/01/2010 5 A
1 06/01/2010 09/01/2010 3 A
1 04/01/2010 15/01/2010 11 B
2 01/02/2010 04/04/2010 62 A
2 01/02/2010 04/04/2010 62 C
英文:
I have a table (T1) which looks like that :
ID Date Event
1 01/01/2010 A
1 04/01/2010 B
1 06/01/2010 A
1 09/01/2010 A
1 15/01/2010 B
2 01/02/2010 A
2 04/04/2010 A
2 01/02/2010 C
2 04/04/2010 C
I would like to calculate the difference between two dates based on "ID" and "Event" Columns. I would need a table (T2) which looks like that :
ID Date Date2 Ndays Event
1 01/01/2010 06/01/2010 5 A
1 06/01/2010 09/01/2010 3 A
1 04/01/2010 15/01/2010 11 B
2 01/02/2010 04/04/2010 62 A
2 01/02/2010 04/04/2010 62 C
答案1
得分: 2
以下是翻译好的部分：
一个简单的Group By查询足以：
SQL查询如下：
SELECT
Events.ID,
Min(Events.Date) AS Date1,
Max(Events.Date) AS Date2,
DateDiff('d',[Date1],[Date2]) AS NDays,
Events.Event
FROM
Events
GROUP BY
Events.ID,
Events.Event;
输出：
编辑：
对于更多记录，使用子查询：
SQL查询如下：
SELECT
Events.ID,
Events.Date AS Date1,
(SELECT Min(T.Date)
FROM Events AS T
WHERE T.ID = Events.ID AND T.Event = Events.Event AND T.Date > Events.Date) AS Date2, DateDiff('d',[Date1],[Date2]) AS NDays,
Events.Event
FROM
Events
GROUP BY
Events.ID,
Events.Date,
Events.Event
HAVING
(SELECT Min(T.Date)
FROM Events AS T
WHERE T.ID = Events.ID AND T.Event = Events.Event AND T.Date > Events.Date) IS NOT NULL
ORDER BY
Events.ID,
Events.Event,
Events.Date;
输出：
英文:
A simple Group By query will do:
SELECT
Events.ID,
Min(Events.Date) AS Date1,
Max(Events.Date) AS Date2,
DateDiff('d',[Date1],[date2]) AS NDays,
Events.Event
FROM
Events
GROUP BY
Events.ID,
Events.Event;
Output:
Edit:
For more records, use a subquery:
SELECT
Events.ID,
Events.Date AS Date1,
(Select Min(T.Date)
From Events As T
Where T.ID = Events.ID And T.Event = Events.Event And T.Date > Events.Date) AS Date2, DateDiff('d',[Date1],[date2]) AS NDays,
Events.Event
FROM
Events
GROUP BY
Events.ID,
Events.Date,
Events.Event
HAVING
(Select Min(T.Date)
From Events As T
Where T.ID = Events.ID And T.Event = Events.Event And T.Date > Events.Date) Is Not Null
ORDER BY
Events.ID,
Events.Event,
Events.Date;
Output:
答案2
得分: 0
使用自连接和聚合：
SELECT t.ID, t.Date,
MIN(tt.Date) AS Date2,
DATEDIFF('d', t.Date, Date2) AS NDays,
t.Event
FROM tablename t INNER JOIN tablename tt
ON tt.ID = t.ID AND tt.Event = t.Event AND t.Date < tt.Date
GROUP BY t.ID, t.Date, t.Event
ORDER BY t.ID, t.Event, t.Date
结果：
ID Date Date2 NDays Event
1 1/1/2010 6/1/2010 5 A
1 6/1/2010 9/1/2010 3 A
1 4/1/2010 15/1/2010 11 B
2 1/2/2010 4/4/2010 62 A
2 1/2/2010 4/4/2010 62 C
英文:
With a self join and aggregation:
SELECT t.ID, t.Date,
MIN(tt.Date) AS Date2,
DATEDIFF('d', t.Date, Date2) AS NDays,
t.Event
FROM tablename t INNER JOIN tablename tt
ON tt.ID = t.ID AND tt.Event = t.Event AND t.Date < tt.Date
GROUP BY t.ID, t.Date, t.Event
ORDER BY t.ID, t.Event, t.Date
Results:
ID Date Date2 NDays Event
1 1/1/2010 6/1/2010 5 A
1 6/1/2010 9/1/2010 3 A
1 4/1/2010 15/1/2010 11 B
2 1/2/2010 4/4/2010 62 A
2 1/2/2010 4/4/2010 62 C
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论