这效果么 ? 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 -------------------------- 1 1 abc 2 2 def 3 3 g
这样也可以、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
像这种的,oracle不可能这般智能。 你最好配合程序来实现的, group by时,要把select 中全写出来
如果按10条按20条 可以该成存储了中间的case when使用循环拼接,3换成参数
结合上面的表中的数据,lz是想到达到什么样的效果?你group by 后,最终是要做什么的?请明细下
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
--------------------------
1 1 abc
2 2 def
3 3 g
(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
你最好配合程序来实现的,
group by时,要把select 中全写出来