select MC1,ZS1,KC1,MC2,ZS2,KC2,MC3,ZS3,KC3
from table
想要按A组，B组，C组来分组
得到结果是
------解决思路----------------------
with tt as
(select 'A组' MC1,'1' ZS1,'烹饪' KC1,'B组' MC2,'3' ZS2,'生物' KC2,'A组' MC3,'1' ZS3,'XX学' KC3 from dual union all
select 'A组','2','列毛','C组','1','化学','B组','1','政治' from dual union all
select 'A组','3','马克思',null,null,null,'B组','2','计算机' from dual union all
select 'B组','2','历史',null,null,null,'C组','2','物理' from dual union all
select 'C组','2','挖掘机',null,null,null,null,null,null from dual)
select nvl(mc1,nvl(mc2,mc3)) mc1,zs1,kc1,zs2,kc2,zs3,kc3 from (select row_number() over (partition by mc1 order by mc1,zs1) rn,mc1,zs1,kc1 from tt) A
full outer join (select row_number() over (partition by mc2 order by mc2,zs2) rn,mc2,zs2,kc2 from tt) B on B.mc2=A.MC1 and B.rn=A.rn
full outer join (select row_number() over (partition by mc3 order by mc3,zs3) rn,mc3,zs3,kc3 from tt) C on C.mc3=A.MC1 and C.rn=A.rn
where not (mc1 is null and mc2 is null and mc3 is null)
order by mc1,zs1;
另：上次你讲的排序问题，我这里一并重新写一下：
with Tb as (
select 'A组' mc,'1' as zs,3 as js,'马克思' as kc from dual union all
select 'A组' mc,'1' as zs,1 as js,'烹饪' as kc from dual union all
select 'A组' mc,'1' as zs,2 as js,'列毛' as kc from dual union all
select 'A组' mc,'3' as zs,1 as js,'XX学' as kc from dual union all
select 'C组' mc,'3' as zs,2 as js,'物理' as kc from dual union all
select 'B组' mc,'1' as zs,2 as js,'历史' as kc from dual union all
select 'C组' mc,'1' as zs,2 as js,'挖掘机' as kc from dual union all
select 'B组' mc,'3' as zs,1 as js,'政治' as kc from dual union all
select 'B组' mc,'2' as zs,3 as js,'生物' as kc from dual union all
select 'C组' mc,'2' as zs,1 as js,'化学' as kc from dual union all
select 'B组' mc,'3' as zs,2 as js,'计算机' as kc from dual)
select A.mc as zs1_mc,A.js as zs1_js,A.kc as zs1_kc,B.mc as zs2_mc,B.js as zs2_js,B.kc as zs_kc,C.mc as zs3_mc,C.js as zs3_js,C.kc as zs3_kc
from (select row_number() over (order by mc,js) rn,mc,zs,js,kc from tb where zs=1) A
left join (select row_number() over (order by mc,js) rn,mc,zs,js,kc from tb where zs=2) B on A.rn=B.rn
left join (select row_number() over (order by mc,js) rn,mc,zs,js,kc from tb where zs=3 ) C on A.rn=C.rn
order by 1,2;
------解决思路----------------------
可以解决的：就是第一列缺失的先补上 即原第一个from tt 中添加入两个union,把第二列与第三列的列名加入，当然最后的判断需变成 where not (kc1 is null and kc2 is null and kc3 is null)
with tt as
(select 'A组' MC1,'1' ZS1,'烹饪' KC1,'B组' MC2,'3' ZS2,'生物' KC2,'A组' MC3,'1' ZS3,'XX学' KC3 from dual union all
select 'A组','2','列毛','C组','1','化学','B组','1','政治' from dual union all
select 'A组','3','马克思','D组','4','天文','B组','2','计算机' from dual union all
select 'B组','2','历史',null,null,null,'C组','2','物理' from dual union all
select 'C组','2','挖掘机',null,null,null,'D组','4','考古' from dual)
select nvl(mc1,nvl(mc2,mc3)) mc1,zs1,kc1,zs2,kc2,zs3,kc3 from (select row_number() over (partition by mc1 order by mc1,zs1) rn,mc1,zs1,kc1 from
(select mc1,zs1,kc1 from tt union all select mc2,null,null from tt where not exists (select 1 from tt uu where mc2=tt.mc1) union all select mc3,null,null from tt where not exists (select 1 from tt uu where mc3=tt.mc1)) ) A
full outer join (select row_number() over (partition by mc2 order by mc2,zs2) rn,mc2,zs2,kc2 from tt) B on B.mc2=A.MC1 and B.rn=A.rn
full outer join (select row_number() over (partition by mc3 order by mc3,zs3) rn,mc3,zs3,kc3 from tt) C on C.mc3=A.MC1 and C.rn=A.rn
where not (kc1 is null and kc2 is null and kc3 is null)
order by mc1,zs1;;