编写一个Oracle SQL查询以获得三个或更多连续的ID,这些ID的雇员数为100.例如,在下面的数据中,我们需要ID为-5、6、7、8的行 说明:ID为5、6、7、8的行是连续的,并且有>;100个TOTAL_EMPLOYEE.但是,即使TOTAL_Employees>;100也没有 Select id 10或12,因为它们不是连续的.
输入数据数据
create table employee(id integer, enroll_date date, total_employees integer);
insert into employee values (1,to_date('01-04-2023','DD-MM-YYYY'),10);
insert into employee values (2,to_date('02-04-2023','DD-MM-YYYY'),109);
insert into employee values (3,to_date('03-04-2023','DD-MM-YYYY'),150);
insert into employee values (4,to_date('04-04-2023','DD-MM-YYYY'),99);
insert into employee values (5,to_date('05-04-2023','DD-MM-YYYY'),145);
insert into employee values (6,to_date('06-04-2023','DD-MM-YYYY'),1455);
insert into employee values (7,to_date('07-04-2023','DD-MM-YYYY'),199);
insert into employee values (8,to_date('08-04-2023','DD-MM-YYYY'),188);
insert into employee values (10,to_date('10-04-2023','DD-MM-YYYY'),188);
insert into employee values (12,to_date('12-04-2023','DD-MM-YYYY'),10);
insert into employee values (13,to_date('13-04-2023','DD-MM-YYYY'),200);
已try 在下面获取连续的组和员工计数标志,但无法获得所需结果.
select id, enroll_date,total_employees,
case when total_employees>100 then 1 else 0 end emp_flag,
SUM(case when total_employees>100 then 1 else 0 end) OVER (ORDER BY id) AS grp,
id - row_number() over(order by id) as diff, -- group consecutive id's
ROW_NUMBER() OVER (PARTITION BY CASE WHEN total_employees > 100
THEN 1 ELSE 0 END ORDER BY enroll_date) as sal_rn,
id - ROW_NUMBER() OVER (PARTITION BY CASE WHEN total_employees > 100
THEN 1 ELSE 0 END ORDER BY enroll_date) AS sal_grp
from employee
;