For example: student A has three records, three time respectively, 2017-1-1, 2017-3-1, 2017-10-1,
Then straight to enrolling number 1;
There are three records student B: three time respectively, 2017-1-1, 2017-3-1, 2017-8-30,
So continuous to enrolling number is 2;


Please help!!!!!

CodePudding user response:

 with t1 as 
(
Select the 'A' c1 and c2 from '2017-1-1' dual union all 
Select the 'A' c1 and c2 from '2017-10-1' dual union all 
Select the 'A' c1 and c2 from '2017-3-1' dual union all 

Select 'B' c1, c2 from '2017-1-1' dual union all 
Select 'B' c1, c2 from '2017-3-1' dual union all 
Select 'B' c1, c2 from '2017-8-30' dual 
) 
, t2 as 
(
The select row_number () over (order by c1, to_date (c2, '- dd yyyy - mm)) rn, c1, to_date (c2,' yyyy - mm - dd) c2 from t1 
) 
The select a.c 1, sum (case when 1 is not null and biggest months_between (biggest 2, 2) a.c & lt; 6 then 1 else=0 end) 
The from t2 a 
Left the join t2 on a.c b 1=1 and biggest a.r n=b.r n - 1 
Group by a.c 1 

CodePudding user response:

A bit strong , but there is a problem, that is for all newspaper reading times, and in a row, I'd like to ask this demand is the largest number of consecutive
When five time is respectively 2017-1-1,
In the 2017-3-1 s,
In the 2017-10-1 s,
In the 2017-11-1 s,
In 2018-8-1, should be 2, not 3

CodePudding user response:

reference 1st floor ZBDZJX response:

 with t1 as 
(
Select the 'A' c1 and c2 from '2017-1-1' dual union all 
Select the 'A' c1 and c2 from '2017-10-1' dual union all 
Select the 'A' c1 and c2 from '2017-3-1' dual union all 

Select 'B' c1, c2 from '2017-1-1' dual union all 
Select 'B' c1, c2 from '2017-3-1' dual union all 
Select 'B' c1, c2 from '2017-8-30' dual 
) 
, t2 as 
(
The select row_number () over (order by c1, to_date (c2, '- dd yyyy - mm)) rn, c1, to_date (c2,' yyyy - mm - dd) c2 from t1 
) 
The select a.c 1, sum (case when 1 is not null and biggest months_between (biggest 2, 2) a.c & lt; 6 then 1 else=0 end) 
The from t2 a 
Left the join t2 on a.c b 1=1 and biggest a.r n=b.r n - 1 
Group by a.c 1 

is a bit strong, but there is a problem, it is continuous at all times and pray, I'd like to ask this demand is the largest number of consecutive
When five time is respectively 2017-1-1,
In the 2017-3-1 s,
In the 2017-10-1 s,
In the 2017-11-1 s,
In 2018-8-1, should be 2, not 3

CodePudding user response:

refer to the second floor Dack_Huang response:

is a bit strong , but there is a problem, that is for all newspaper reading times, and in a row, I'd like to ask this demand is the largest number of consecutive
When five time is respectively 2017-1-1,
In the 2017-3-1 s,
In the 2017-10-1 s,
In the 2017-11-1 s,
In 2018-8-1, should be 2 instead of 3

The five dates, calculated should be 2,
Unless you are 2017-1-1, 2017-3-1, 2017-10-1, 2017-11-1, 2017-12-1 such five date, before 2, 3 a set of, want to calculate the result is 2,

CodePudding user response:

reference 4 floor ZBDZJX response:

Quote: refer to the second floor Dack_Huang response:

A bit strong , but there is a problem, that is for all newspaper reading times, and in a row, I'd like to ask this demand is the largest number of consecutive
When five time is respectively 2017-1-1,
In the 2017-3-1 s,
In the 2017-10-1 s,
In the 2017-11-1 s,
In 2018-8-1, should be 2 instead of 3

The five dates, calculated should be 2,
Unless you are 2017-1-1, 2017-3-1, 2017-10-1, 2017-11-1, 2017-12-1 such five date, before 2, 3 a set of, want to calculate the result is 2,

Carefully thought for a moment, the feeling is logical or not,

CodePudding user response:

With t1 as
(
Select the 'A' id1, the date '2017-1-1' the start_date from dual union all
Select the 'A' id1, the date '2017-8-1' the start_date from dual union all
Select the 'A' id1, the date '2017-10-1' the start_date from dual union all
Select the 'A' id1, the date '2017-11-1' the start_date from dual union all
Select the 'A' id1, the date '2017-12-1' the start_date from dual union all
Select the 'A' id1, the date '2018-7-1' the start_date from dual union all
Select the 'A' id1, the date '2018-10-1' the start_date from dual union all
Select the 'A' id1, the date '2018-11-1' the start_date from dual union all
Select 'B' id1, the date '2017-12-1' the start_date from dual union all
Select 'B' id1, the date '2018-7-1' the start_date from dual union all
Select 'B' id1, the date '2018-10-1' the start_date from dual union all
Select 'B' id1, the date '2018-11-1' the start_date from dual UNION ALL
Select 'B' id1, the date '2019-10-1' the start_date from dual union all
Select 'B' id1, the date '2020-11-1' the start_date from dual
)
, t2 as
(select id1
, the start_date start_day
And NVL (lead (the start_date) over (partition by id1 order by the start_date), the date '2999-12-31') end_day
The row_number () over (partition by id1 order by the start_date) rn
The from t1)
Select
Id1
, Max (num1 + 1) - the largest number of consecutive
The from (
SELECT ID1, TYPE1, COUNT (1) num1
The FROM (select an rn - rn1 TYPE1
, a1. *
The from (
Select a. *
The row_number () over (partition by id1 order by start_day) rn1
The from (select t2 id1
T2. Rn
T2. Start_day
T2. End_day
The case when add_months (t2) start_day, 6) & gt; T2. End_day then 1 else 0 end lx_rn
The from t2
Where a case when add_months (t2) start_day, 6) & gt; T2. End_day then 1 else 0 end & gt; 0
) a
A1)
A2)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull