Take time not sent over 60 days the data-CodePudding

For example, I search the article n the data in table a, there is a field in a table is time time, now id=1, according to the condition of certain search article n data, these data the order by the time according to the time sequence, the search data in the time interval of not more than 60 days of the name in the data field code, consult a great god, and how to write the SQL development need!!!!!

CodePudding user response:

This refers to the adjacent records intervals less than 60 days?

CodePudding user response:

 
With a as 
(select the 'a' name, code, the date '2015-1-1' time from dual union all 
Select 'b' name, 2 code, the date '2015-1-6' time from dual union all 
Select the 'c' name, code 3, the date '2016-1-9' time from dual union all 
Select the 'd' name, code, the date '2016-2-1' time from dual union all 
Select the 'e' name, code, the date '2016-3-7' time from dual union all 
Select the 'f' name, 6 code, the date '2016-8-1' time from dual 
), 
T as 
(select name, code, time, row_number () over (order by time) rn 
From a 
- where id=1 
) 
Select t1. The name, t1 code, t2. * 
The from t t1, t t2 
Where a t1. Rn=t2. Rn 1 
And (abs (t1) time - t2) time) & lt;=60 or abs (t2) time - t1) time) & lt;=60) 
The order by 1 
;

Also lead and lag function can also be used to meet your requirements, specific usage such as search or you # 1

CodePudding user response:

refer to the second floor js14982 response:

 

A bit of a problem, change
[code=SQL] with the as a
(select the 'a' name, code, the date '2015-1-1' time from dual union all
Select 'b' name, 2 code, the date '2015-1-6' time from dual union all
Select the 'c' name, code 3, the date '2016-1-9' time from dual union all
Select the 'd' name, code, the date '2016-2-1' time from dual union all
Select the 'e' name, code, the date '2016-3-7' time from dual union all
Select the 'f' name, 6 code, the date '2016-8-1' time from dual
),
T as
(select name, code, time, row_number () over (order by time) rn
From a
- where id=1
)
Select a t1. The name, t1. The code
The from t t1, t t2
Where a t1. Rn=t2. Rn 1
And (t1) time - t2) time<=60 or abs (t2) time - t1) time) & lt;=60)
The order by 1
;

CodePudding user response:

Using a window function:

 
With a as 
(select the 'a' name, code, the date '2015-1-1' time from dual union all 
Select 'b' name, 2 code, the date '2015-1-6' time from dual union all 
Select the 'c' name, code 3, the date '2016-1-9' time from dual union all 
Select the 'd' name, code, the date '2016-2-1' time from dual union all 
Select the 'e' name, code, the date '2016-3-7' time from dual union all 
Select the 'f' name, 6 code, the date '2016-8-1' time from dual 
), 
T the as (select name, code, time, count (1) over (order by time desc range interval '60 s' day preceding) 
+ count (1) over (the order by the time the range interval '60 s' day preceding) cc from a) 
Select the name, code, the time from t where cc> 2;

CodePudding user response:

Try analysis function to realize
Refer to http://blog.csdn.net/thinkscape/article/details/8290894

CodePudding user response:

reference 1st floor ghx287524027 response:

this is refers to the adjacent records intervals less than 60 days?

Interval with q, who and who?