根据条件查找Oracle SQL中的日期差异
根据条件查找Oracle SQL中的日期差异
提问于 2019-07-09 01:37:51
我有一张桌子
table1
tran_id user_id start_date end_date
1 100 01-06-2018 18-06-2018
2 100 14-06-2018 14-06-2018
4 100 19-07-2018 19-07-2018
7 101 05-01-2018 06-01-2018
9 101 08-01-2018 08-01-2018
3 101 03-01-2018 03-01-2018演示- Link
逻辑如下：
我需要找出按start_date排序的成员的两个trans_id之间的天差，其中没有重叠的start_date和end_date。
我们需要检查一次处理一条记录的用户的最大end_date。
逻辑是这样的：
对于成员：
- 按
user_id和start_date trans_id= 1，end_date= 18-06-2018，user_id=max_end_date= 2，end_date= 14-06-2018，end_date<max_end_date，movemax_end_date= 3，end_date= 19-07-2018，<user_id>D22max_end_date，在transidfrom=1的输出中添加一条记录(因为这是max_end_date)transidto=4的记录(因为这是transidfromtransidto_start_date>max_end_date)transidfrom_end_date= 18-06-2018的记录，所以选择transidfromtransidto_start_date= 19-07-2018的trans_id的trans_id，选择transidfrom_end_date
= trans_id - transidto_start_date的trans_id的start_date
输出将如下所示：
table2
my_id transidfrom transidto transidfrom_end_date transidto_start_date datediff
1 1 4 18-06-2018 19-07-2018 31
2 3 7 03-01-2018 05-01-2018 2
3 7 9 06-01-2018 08-01-2018 2在Oracle SQL中，有没有一种方法可以在一个查询中做到这一点？
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-07-09 03:10:50
如果我正确地理解了您的需求，那么也许下面的代码就可以做到：
FSITJA@db01 2019-07-08 12:08:59> with table1(tran_id, user_id, start_date, end_date) as (
2 select 1, 100, date '2018-06-01', date '2018-06-18' from dual union all
3 select 2, 100, date '2018-06-14', date '2018-06-14' from dual union all
4 select 4, 100, date '2018-07-19', date '2018-07-19' from dual union all
5 select 7, 101, date '2018-01-05', date '2018-01-06' from dual union all
6 select 9, 101, date '2018-01-08', date '2018-01-08' from dual union all
7 select 3, 101, date '2018-01-03', date '2018-01-03' from dual )
8 select rownum as my_id,
9 tran_id as transidfrom,
10 next_tran_id as transidto,
11 end_date as transidfrom_end_date,
12 next_end_date as transidto_start_date,
13 datediff
14 from (select tran_id,
15 user_id,
16 start_date,
17 end_date,
18 lead(tran_id) over (partition by user_id order by end_date) next_tran_id,
19 lead(start_date) over (partition by user_id order by end_date) next_end_date,
20 lead(start_date) over (partition by user_id order by end_date) - end_date datediff
21 from table1)
22 where datediff > 0;
MY_ID TRANSIDFROM TRANSIDTO TRANSIDFROM_END_DAT TRANSIDTO_START_DAT DATEDIFF
---------- ----------- ---------- ------------------- ------------------- ----------
1 1 4 2018-06-18 00:00:00 2018-07-19 00:00:00 31
2 3 7 2018-01-03 00:00:00 2018-01-05 00:00:00 2
3 7 9 2018-01-06 00:00:00 2018-01-08 00:00:00 2
3 rows selected.页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56940002
复制相关文章
相似问题