一个窗口函数搞定的事,用什么序列。userName flg code decode(code,0,0,1) sum(decode(code,0,0,1)) over(order by rowid) a 2 0 0 0 b 1 c1 1 1 c 2 0 0 1 d 2 0 0 1 e 1 c2 1 2 f 2 0 0 2
你这个投机取巧了不是我想要的结果。。 我上面的0是代表任意一个字符串。。为了防止误解。。那我写清楚吧变换前: userName flg code a 2 c3 b 1 c1 c 2 c4 d 2 c5 e 1 c2 f 2 c6变换后 userName flg code a 2 " " -- 一个space b 1 c1 c 2 c1 d 2 c1 e 1 c2 f 2 c2
with data as ( select 'a' user_name, '2' flg, '0' code from dual union all select 'b', '1', 'c1' from dual union all select 'c', '2', '0' from dual union all select 'd', '2', '0' from dual union all select 'e', '1', 'c2' from dual union all select 'f', '2', '0' from dual union all select 'g', '2', '0' from dual union all select 'h', '1', 'c3' from dual union all select 'i', '2', '0' from dual ) select user_name, flg, code from (select a.user_name, a.flg, nvl(b.code, ' ') code, row_number() over(partition by a.user_name order by b.user_name desc) rn from data a, (select * from data where flg = 1) b where a.user_name >= b.user_name(+)) where rn = 1
a 2 0 0 0
b 1 c1 1 1
c 2 0 0 1
d 2 0 0 1
e 1 c2 1 2
f 2 0 0 2
我上面的0是代表任意一个字符串。。为了防止误解。。那我写清楚吧变换前:
userName flg code
a 2 c3
b 1 c1
c 2 c4
d 2 c5
e 1 c2
f 2 c6变换后
userName flg code
a 2 " " -- 一个space
b 1 c1
c 2 c1
d 2 c1
e 1 c2
f 2 c2
(
select 'a' user_name, '2' flg, '0' code from dual union all
select 'b', '1', 'c1' from dual union all
select 'c', '2', '0' from dual union all
select 'd', '2', '0' from dual union all
select 'e', '1', 'c2' from dual union all
select 'f', '2', '0' from dual union all
select 'g', '2', '0' from dual union all
select 'h', '1', 'c3' from dual union all
select 'i', '2', '0' from dual
)
select user_name, flg, code
from (select a.user_name,
a.flg,
nvl(b.code, ' ') code,
row_number() over(partition by a.user_name order by b.user_name desc) rn
from data a, (select * from data where flg = 1) b
where a.user_name >= b.user_name(+))
where rn = 1