如果我有一个带有 Name 和 Creation_Date 列的表,看起来像
Name, Creation
Sam, 01/01/2020
Sam, 01,02,2020
Jack, 12/31/2019
Jack, 10/21/2018
Jack, 12/30/2019
Hank, 01/01/2019
我想要的是,如果名称匹配并且该名称的创建日期在 +/- 1 天内,我想获取这些行。在这种情况下,我想要 4 行数据作为输出。我只能在特定范围内过滤,但不能在名称级别内过滤。
编辑: 预期行为:
Name, Creation
Sam, 01/01/2020
Sam, 01,02,2020
Jack, 12/31/2019
Jack, 10/21/2018
自助加入可能会有所帮助。
SQL> with test (name, creation) as
2 (select 'Sam' , date '2020-01-01' from dual union all
3 select 'Sam' , date '2020-01-02' from dual union all
4 select 'Jack', date '2019-12-31' from dual union all
5 select 'Jack', date '2018-10-21' from dual union all
6 select 'Jack', date '2019-12-30' from dual union all
7 select 'Hank', date '2019-01-01' from dual
8 )
9 select a.name, a.creation
10 from test a join test b on a.name = b.name
11 and abs(a.creation - b.creation) = 1
12 order by name, creation;
NAME CREATION
---- ----------
Jack 12/30/2019
Jack 12/31/2019
Sam 01/01/2020
Sam 01/02/2020
SQL>
(顺便说一句,我认为你把错误的杰克作为所需的输出;不是2018年的那个,而是2019年的那个(。
您可以使用分析函数。 例如;
select t.*
from (select t.*,
count(*) over (partition by name
range between interval '1' day preceding and interval '1' day following
) as cnt
from t
) t
where cnt > 1;