How to use SQL to change the value of the current record after two-CodePudding

Recently met a strange business requirements, there is a table by the two fields, one is the ID of a FLG is, when a record FLG is Y, then after two records of FLG value may be N,
The following is the desired result:

ID=3 records, for example, FLG is Y, then ID is the record of 4 and 5 FLG will become N, because only two records after considering the change of the FLG, so ID=5 FLG is Y, the same, then ID=6 and 7 of the FLG to become N, so on. If the FLG is N, leave directly on

The following is a table of data

 
Select 1 as ID, 'N' as FLG from dual union all 
Select 2 as ID, 'Y' as FLG from dual union all 
Select 3 as ID, 'Y' as FLG from dual union all 
Select 4 as ID, 'Y' as FLG from dual union all 
Select 5 as ID, 'Y' as FLG from dual union all 
Select 6 as ID, 'Y' as FLG from dual union all 
The select 7 as ID, 'Y' as FLG from dual union all 
8 as ID, select 'Y' as FLG from dual union all 
The select 9 as ID, 'N' as FLG from dual union all 
The select 10 as ID, 'Y' as FLG from dual union all 
Select 11 as ID, 'N' as FLG from dual union all 
The select 12 as ID, 'Y' as FLG from dual union all 
The select 13 as ID, 'N' as FLG from dual union all 
Select 14 as ID, 'N' as FLG from dual union all 
The select 15 as ID, 'N' as FLG from dual union all 
The select 16 as ID, 'Y' as FLG from dual union all 
17 as ID, select 'N' as FLG from dual union all 
18 as ID, select 'N' as FLG from dual union all 
Select the 19th as ID, 'N' as FLG from dual union all 
Select 20 as ID, 'N' as FLG from dual union all 
Select the 21st as ID, 'N' as FLG from dual union all 
The select 22 as ID, 'Y' as FLG from dual union all 
23 as ID, select 'Y' as FLG from dual

CodePudding user response:

 with tab1 as (
Select 1 as ID, 'N' as FLG from dual union all 
Select 2 as ID, 'Y' as FLG from dual union all 
Select 3 as ID, 'Y' as FLG from dual union all 
Select 4 as ID, 'Y' as FLG from dual union all 
Select 5 as ID, 'Y' as FLG from dual union all 
Select 6 as ID, 'Y' as FLG from dual union all 
The select 7 as ID, 'Y' as FLG from dual union all 
8 as ID, select 'Y' as FLG from dual union all 
The select 9 as ID, 'N' as FLG from dual union all 
The select 10 as ID, 'Y' as FLG from dual union all 
Select 11 as ID, 'N' as FLG from dual union all 
The select 12 as ID, 'Y' as FLG from dual union all 
The select 13 as ID, 'N' as FLG from dual union all 
Select 14 as ID, 'N' as FLG from dual union all 
The select 15 as ID, 'N' as FLG from dual union all 
The select 16 as ID, 'Y' as FLG from dual union all 
17 as ID, select 'N' as FLG from dual union all 
18 as ID, select 'N' as FLG from dual union all 
Select the 19th as ID, 'N' as FLG from dual union all 
Select 20 as ID, 'N' as FLG from dual union all 
Select the 21st as ID, 'N' as FLG from dual union all 
The select 22 as ID, 'Y' as FLG from dual union all 
23 as ID, select 'Y' as FLG from dual 
) 
, t1 (id, FLG flg1, lead1) as (
The select t0. Id, t0. FLG, t0. FLG flg1, t0. FLG lead1 from tab1 t0 the where t0. Id=1 
Union all 
Select t2. Id, t2. FLG, decode (' Y ', t1 flg1, 'N', t1. Lead1, 'N', t2. FLG), t1. Flg1 from tab1 t2, t1 
Where t2. Id=t1. Id + 1 
) 
Select * from t1;

Blind to write, you are in look at change

CodePudding user response:

 
- also provides a train of thought: 
As with t (
Select 1 as ID, 'N' as FLG from dual union all 
Select 2 as ID, 'Y' as FLG from dual union all 
Select 3 as ID, 'Y' as FLG from dual union all 
Select 4 as ID, 'Y' as FLG from dual union all 
Select 5 as ID, 'Y' as FLG from dual union all 
Select 6 as ID, 'Y' as FLG from dual union all 
The select 7 as ID, 'Y' as FLG from dual union all 
8 as ID, select 'Y' as FLG from dual union all 
The select 9 as ID, 'N' as FLG from dual union all 
The select 10 as ID, 'Y' as FLG from dual union all 
Select 11 as ID, 'N' as FLG from dual union all 
The select 12 as ID, 'Y' as FLG from dual union all 
The select 13 as ID, 'N' as FLG from dual union all 
Select 14 as ID, 'N' as FLG from dual union all 
The select 15 as ID, 'N' as FLG from dual union all 
The select 16 as ID, 'Y' as FLG from dual union all 
17 as ID, select 'N' as FLG from dual union all 
18 as ID, select 'N' as FLG from dual union all 
Select the 19th as ID, 'N' as FLG from dual union all 
Select 20 as ID, 'N' as FLG from dual union all 
Select the 21st as ID, 'N' as FLG from dual union all 
The select 22 as ID, 'Y' as FLG from dual union all 
23 as ID, select 'Y' as FLG from dual 
) 
Select * from t 
The model 
Dimension by (ID) 
Measures (FLG, 'N' FLG1) 
Rules (FLG1 [ID]=case when FLG1 [CV () - 2]='Y' or FLG1 [CV () - 1]='Y' then 'N' else FLG [CV ()] end);