Create table test
(
Id varchar2 (20) not null,
Day varchar2 (20) not null,
The value varchar2 (20) not null
)
Insert into test values (' 1 ', '20080309', 'b');
Insert into test values (' 1 ', '20080306', 'b');
Insert into test values (' 1 ', '20080305', 'b');
Insert into test values (' 1 ', '20080304', 'a');
Insert into test values (' 1 ', '20080303', 'a');
Insert into test values (' 2 ', '20080309', 'b');
Insert into test values (' 2 ', '20080306', 'b');
Insert into test values (' 2 ', '20080305', 'a');
Insert into test values (' 2 ', '20080304', 'a');
Insert into test values (' 2 ', '20080303', 'a');
Insert into test values (' 3 ', '20080309', 'a');
Insert into test values (' 3 ', '20080306', 'b');
Insert into test values (' 3 ', '20080305', 'b');
Insert into test values (' 3 ', '20080304', 'a');
Insert into test values (' 3 ', '20080303', 'a');
commit;
Select id, count (*)
The from (
Select the test. *
The row_number () over (partition by id order by day desc) rn
From the test where value='https://bbs.csdn.net/topics/b' and to_date (day, '- dd yyyy - mm) & lt;=the date '2008-03-30'
)
Where to_date (day, 'yyyy - mm - dd) + rn=the date' 2008-03-09 '+ 1
Group by id.
Because Saturday and Sunday didn't date with interval data, on March 9, think through calculation value=https://bbs.csdn.net/topics/b successive occurrences, get data as follows:
Id the count (*)
1 1
2 1
Could you tell me how to write SQL should be
CodePudding user response:
Top of the ~ ~ ~ ~ ~ ~ ~ ~ ~ ~CodePudding user response:
For database, there is no continuity between the data, you only use a date field, date after continuous is equal to 1 minus the day after a day,This complex SQL stored procedure estimated best cursor, logic is: first, select the order by the value day, grouping and partition by, and open cursor, using a line after subtracting the date of the previous line, close the cursor until the value is greater than 1, this number came out,