用connect by 试试select t.grademc,substr(max(sys_connect_by_path(t.stumc,',')),2) stumc from( select tg.grademc,ts.stumc,row_number() over(partition by tg.grademc order by ts.stumc) rn from tmp_stuinfo ts, tmp_gradeinfo tg where ts.gradeid = tg.gradeid ) t start with t.rn=1 connect by t.grademc = prior t.grademc and t.rn-1 = prior t.rn group by t.grademc;
with a as ( select 1 gradeId,'A'mc2 from dual union all select 2 gradeId,'B'mc2 from dual union all select 3 gradeId,'C'mc2 from dual ), b as (select 1 gradeId,'jack' stuInfo from dual union all select 1,'mali' from dual union all select 1,'marry' from dual union all select 2,'tony' from dual union all select 2,'sack' from dual union all select 2, 'tome' from dual union all select 3 ,'kay' from dual union all select 3 ,'jimy' from dual ) --select mc2,row_number()over(partition by a.gradeid order by a.gradeid) rn,stuinfo from a,b where a.gradeId=b.gradeId select mc2,max(substr(sys_connect_by_path(stuinfo,','),2)) studinfo from ( select mc2,a.gradeid,row_number()over(partition by a.gradeid order by a.gradeid) rn, stuinfo from a,b where a.gradeId=b.gradeId ) a group by mc2 start with rn=1 connect by a.gradeId = prior a.gradeId and rn-1 = prior rn--result: 1 A jack,mali,marry 2 B tony,sack,tome 3 C kay,jimy
select mc2, wmsys.wm_concat(mc) from (select stuid, mc, gradeinfo.gradeid, mc2 from gradeinfo, stuinfo where gradeinfo.gradeId = stuinfo.gradeId) group by mc2;
那要什么 啊?
你试下Left Join这个是左连接。
from(
select tg.grademc,ts.stumc,row_number() over(partition by tg.grademc order by ts.stumc) rn
from tmp_stuinfo ts,
tmp_gradeinfo tg
where ts.gradeid = tg.gradeid
) t
start with t.rn=1
connect by t.grademc = prior t.grademc
and t.rn-1 = prior t.rn
group by t.grademc;
(
select 1 gradeId,'A'mc2 from dual
union all
select 2 gradeId,'B'mc2 from dual
union all
select 3 gradeId,'C'mc2 from dual
),
b as
(select 1 gradeId,'jack' stuInfo from dual
union all
select 1,'mali' from dual
union all
select 1,'marry' from dual
union all
select 2,'tony' from dual
union all
select 2,'sack' from dual
union all
select 2, 'tome' from dual
union all
select 3 ,'kay' from dual
union all
select 3 ,'jimy' from dual
)
--select mc2,row_number()over(partition by a.gradeid order by a.gradeid) rn,stuinfo from a,b where a.gradeId=b.gradeId
select mc2,max(substr(sys_connect_by_path(stuinfo,','),2)) studinfo
from
( select mc2,a.gradeid,row_number()over(partition by a.gradeid order by a.gradeid) rn, stuinfo
from a,b where a.gradeId=b.gradeId ) a
group by mc2
start with rn=1 connect by a.gradeId = prior a.gradeId and rn-1 = prior rn--result:
1 A jack,mali,marry
2 B tony,sack,tome
3 C kay,jimy
from (select stuid, mc, gradeinfo.gradeid, mc2
from gradeinfo, stuinfo
where gradeinfo.gradeId = stuinfo.gradeId)
group by mc2;