select * from GradeInfo a ,学生表 b where a.gradeid = b.gradeid试下
解决方案 »
- 如何用iAnywhere连接Sql Anywhere 12
- 数据库长时间没访问,第一次访问相应特别慢
- oracle怎么快速实现这个?
- 急。。不要让查询的结果进行默认的排序。
- 如何把A表的数据,每隔一天就导出到一个文本文件里?
- 用VB开发软件管理ORACLE的客户端,应该安装那个版本的ORacLE?是选择oracle还是oracle client?
- 如何将Oracle8.0.5中查询到的中文乱码转化为正确的汉字显示。
- int和integer有什么区别吗
- 为什么我在导入一个DMP文件的时候有些视图会提示:创建的对象带有编译性警告.
- 请问怎么实现这样的查询?
- 遇到个sql问题
- 求高效删除两个表中指定字段值相等的记录的方法!!!
那要什么 啊?
你试下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;