请教 分类 group by我有一批数 类似1a2b3c 4d5e6f7g8h9i10j请问 如何按3条分类group by123456 789 10[解决
请教 分类 group by
我有一批数 类似
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j
请问 如何按3条分类group by
123 456 789 10
[解决办法]
这效果么 ？
with t1 as
(
select 1 c1,'a' c2 from dual union all
select 2 c1,'b' c2 from dual union all
select 3 c1,'c' c2 from dual union all
select 4 c1,'d' c2 from dual union all
select 5 c1,'e' c2 from dual union all
select 6 c1,'f' c2 from dual union all
select 7 c1,'g' c2 from dual
)
select rn,replace(wm_concat(c2),',','') c2
from
(
select c2,
case mod(rownum,3) when 2 then (rownum+1)/3
when 1 then (rownum+2)/3
when 0 then rownum/3 end rn
from t1
)
group by rn
order by rn
c2 rn
--------------------------
11abc
22def
33g
[解决办法]
这样也可以、
with t as
(select 1 c1, 'a' c2
from dual
union all
select 2 c1, 'b' c2
from dual
union all
select 3 c1, 'c' c2
from dual
union all
select 4 c1, 'd' c2
from dual
union all
select 5 c1, 'e' c2
from dual
union all
select 6 c1, 'f' c2
from dual
union all
select 7 c1, 'g' c2 from dual)
select t1.c2
[解决办法]
t2.c2
[解决办法]
t3.c2
from t t1
left join t t2
on t1.c1 = t2.c1 - 1
left join t t3
on t2.c1 = t3.c1 - 1
where mod(t1.c1, 3) = 1