我现在有2张表,一张学生等级表GradeInfo,分别有gradeId,mc2个字段,还有张表学生表stuInfo,stuId,mc,gradeId(外键),我想查询每个等级下得对应的学生,
效果是这样的
GradeMc stuMc
A jack,mali,marry
B tony,sack,tome
c kay,jimy各位大侠看下!
效果是这样的
GradeMc stuMc
A jack,mali,marry
B tony,sack,tome
c kay,jimy各位大侠看下!
调试欢乐多
where GradeInfo.gradeId = stuInfo.gradeId group by GradeInfo.mc
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
1 A jack,mali,marry
2 B tony,sack,tome
3 C kay,jimy