最近遇到了一個奇怪的業務需求，有一個表由兩個欄位，一個是ID，一個是FLG，當一條記錄的FLG是Y時，那么后兩條記錄的FLG值要變成N，
以下是期望的結果：
比如，ID=3的記錄，FLG是Y，那么ID是4和5的記錄的FLG都要變成N，因為只考慮后兩條記錄FLG的變化，因此ID=5的FLG是Y，不變，然后ID=6和7的FLG要變成N，依次類推； 如果FLG是N的，就直接保留
以下是表的資料
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
select 7 as ID, 'Y' as FLG from dual union all
select 8 as ID, 'Y' as FLG from dual union all
select 9 as ID, 'N' as FLG from dual union all
select 10 as ID, 'Y' as FLG from dual union all
select 11 as ID, 'N' as FLG from dual union all
select 12 as ID, 'Y' as FLG from dual union all
select 13 as ID, 'N' as FLG from dual union all
select 14 as ID, 'N' as FLG from dual union all
select 15 as ID, 'N' as FLG from dual union all
select 16 as ID, 'Y' as FLG from dual union all
select 17 as ID, 'N' as FLG from dual union all
select 18 as ID, 'N' as FLG from dual union all
select 19 as ID, 'N' as FLG from dual union all
select 20 as ID, 'N' as FLG from dual union all
select 21 as ID, 'N' as FLG from dual union all
select 22 as ID, 'Y' as FLG from dual union all
select 23 as ID, 'Y' as FLG from dual
uj5u.com熱心網友回復：
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
select 7 as ID, 'Y' as FLG from dual union all
select 8 as ID, 'Y' as FLG from dual union all
select 9 as ID, 'N' as FLG from dual union all
select 10 as ID, 'Y' as FLG from dual union all
select 11 as ID, 'N' as FLG from dual union all
select 12 as ID, 'Y' as FLG from dual union all
select 13 as ID, 'N' as FLG from dual union all
select 14 as ID, 'N' as FLG from dual union all
select 15 as ID, 'N' as FLG from dual union all
select 16 as ID, 'Y' as FLG from dual union all
select 17 as ID, 'N' as FLG from dual union all
select 18 as ID, 'N' as FLG from dual union all
select 19 as ID, 'N' as FLG from dual union all
select 20 as ID, 'N' as FLG from dual union all
select 21 as ID, 'N' as FLG from dual union all
select 22 as ID, 'Y' as FLG from dual union all
select 23 as ID, 'Y' as FLG from dual
)
, t1(id, flg, flg1, lead1) as (
select t0.id, t0.flg, t0.flg flg1, t0.flg lead1 from tab1 t0 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;
盲寫的，你在看著改改
uj5u.com熱心網友回復：
--也提供一種思路：
with t 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
select 7 as ID, 'Y' as FLG from dual union all
select 8 as ID, 'Y' as FLG from dual union all
select 9 as ID, 'N' as FLG from dual union all
select 10 as ID, 'Y' as FLG from dual union all
select 11 as ID, 'N' as FLG from dual union all
select 12 as ID, 'Y' as FLG from dual union all
select 13 as ID, 'N' as FLG from dual union all
select 14 as ID, 'N' as FLG from dual union all
select 15 as ID, 'N' as FLG from dual union all
select 16 as ID, 'Y' as FLG from dual union all
select 17 as ID, 'N' as FLG from dual union all
select 18 as ID, 'N' as FLG from dual union all
select 19 as ID, 'N' as FLG from dual union all
select 20 as ID, 'N' as FLG from dual union all
select 21 as ID, 'N' as FLG from dual union all
select 22 as ID, 'Y' as FLG from dual union all
select 23 as ID, 'Y' as FLG from dual
)
select * from t
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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/6826.html
標籤：開發
上一篇：求關系樹每一之間級關系
下一篇：oracle 特殊時間格式問題