create function get_grouping(p_col1 in varchar2,p_col2 in varchar2,p_col3 in varchar2)
return varchar2
v_col1 varchar2(20);
v_col2 varchar2(20);
v_col3 varchar2(20);
as
begin
if p_col1 is null then
v_col1:='0';
else
for i in 65..90 loop
if ascii(p_col1)=i then
v_col1:=i-64;
exit;
end if;
end loop;
end if;
if p_col2 is null then
v_col2:='0';
else
for i in 97..122 loop
if ascii(p_col2)=i then
v_col2:=i-96;
exit;
end loop;
end if;
v_col3:=decode(p_col3,'!','1','@','2','0');
return v_col1||'.'||v_col2||'.'||v_col3;
end;
/ select max(get_grouping(col1,col2,col3)),col1, col2, col3, sum(wgt) wgt
from wanghj
group by rollup(col1, col2, col3)
return varchar2
v_col1 varchar2(20);
v_col2 varchar2(20);
v_col3 varchar2(20);
as
begin
if p_col1 is null then
v_col1:='0';
else
for i in 65..90 loop
if ascii(p_col1)=i then
v_col1:=i-64;
exit;
end if;
end loop;
end if;
if p_col2 is null then
v_col2:='0';
else
for i in 97..122 loop
if ascii(p_col2)=i then
v_col2:=i-96;
exit;
end loop;
end if;
v_col3:=decode(p_col3,'!','1','@','2','0');
return v_col1||'.'||v_col2||'.'||v_col3;
end;
/ select max(get_grouping(col1,col2,col3)),col1, col2, col3, sum(wgt) wgt
from wanghj
group by rollup(col1, col2, col3)
- - - ----------
A a ! 100
A b ! 50
A a @ 20
A b @ 300
B a ! 20
B a @ 5
B b ! 25
B b @ 90已选择8行。已用时间: 00: 00: 00.16
09:16:19 SQL> select decode(b.col1,null,0,b.col4)||'.'||decode(c.col2,null,0,c.c
ol5)||'.'||decode(d.col3,null,0,d.col6) xxx,a.col1,a.col2,a.col3,a.wgt
09:16:26 2 from
09:16:26 3 (select col1, col2, col3, sum(wgt) wgt from wanghj group by rollup
(col1, col2, col3)) a,
09:16:26 4 (select col1,row_number() over (order by col1) col4 from (select d
istinct col1 from wanghj)) b,
09:16:26 5 (select col2,row_number() over (order by col2) col5 from (select d
istinct col2 from wanghj)) c,
09:16:26 6 (select col3,row_number() over (order by col3) col6 from (select d
istinct col3 from wanghj)) d
09:16:26 7 where a.col1=b.col1(+) and a.col2=c.col2(+) and a.col3=d.col3(+) o
rder by col1,col2,col3;XXX C C C WGT
-------------------- - - - ----------
1.1.1 A a ! 100
1.1.2 A a @ 20
1.1.0 A a 120
1.2.1 A b ! 50
1.2.2 A b @ 300
1.2.0 A b 350
1.0.0 A 470
2.1.1 B a ! 20
2.1.2 B a @ 5
2.1.0 B a 25
2.2.1 B b ! 25
2.2.2 B b @ 90
2.2.0 B b 115
2.0.0 B 140
0.0.0 610已选择15行。已用时间: 00: 00: 00.46
decode(col2,null,0,dense_rank() over (partition by col1 order by col2)) || '.' ||
decode(col3,null,0,dense_rank() over (partition by col1,col2 order by col3)) xxx,
col1, col2, col3, sum(wgt) wgt
from wanghj
group by rollup(col1, col2, col3);