select s.studentid 学号,s.name 姓名,k.store 分数,c.calssname 课程名, decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) 学分, sum(store) over (PARTITION BY s.studentid) 总分 (store-50)%10 学分 from student s,store k,class c where s.studentid=k.studentid and c.classid=k.classid
刚刚写错了 select s.studentid 学号,s.name 姓名,k.store 分数,c.calssname 课程名, decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) 学分, sum(store) over (PARTITION BY s.studentid) 总分 from student s,store k,class c where s.studentid=k.studentid and c.classid=k.classid
over (PARTITION BY s.studentid) 是什么意思 能给我讲讲吗~!!~ 这就去解贴
原来是求总学分,再改下 select s.studentid,s.name,k.store,c.calssname, decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) xf, sum(decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY s.studentid) zf from student s,store k,class c where s.studentid=k.studentid and c.classid=k.classidover 是Oracle的分析函数 sum(decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY s.studentid) 表示按照学号对学分进行分区统计得到总学分 例如总学分查询结果如下: 学号 学分 总学分 1 1 3 1 2 3 2 2 5 2 3 5附录: over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数 over(partition by deptno)按照部门分区 over(order by salary range between 50 preceding and 150 following) 每行对应的数据窗口是之前行幅度值不超过50,之后行幅度值不超过150 over(order by salary rows between 50 preceding and 150 following) 每行对应的数据窗口是之前50行,之后150行 over(order by salary rows between unbounded preceding and unbounded following) 每行对应的数据窗口是从第一行到最后一行,等效: over(order by salary range between unbounded preceding and unbounded following)
SELECT S.ID 學号, S.NAME 姓名, DECODE(S.CLASSNAME,'ENGLISH','ENGLISH') 課程1, DECODE(S.CLASSNAME,'ENGLISH',S.STORE) 課程1學分, DECODE(S.CLASSNAME,'MATH','MATH') 課程2, DECODE(S.CLASSNAME,'MATH',S.STORE) 課程2學分, DECODE(S.CLASSNAME,'OPERATION','OPERATION') 課程3, DECODE(S.CLASSNAME,'OPERATION',S.STORE) 課程3學分, DECODE(S.CLASSNAME,'CHINESE','CHINESE') 課程4, DECODE(S.CLASSNAME,'CHINESE',S.STORE) 課程4學分, DECODE(S.CLASSNAME,'DATABASE','DATABASE') 課程5, DECODE(S.CLASSNAME,'DATABASE',S.STORE) 課程5學分 FROM (SELECT STUDENT.NAME,(STUDENT.STUDENTID)AS ID,DECODE(TRUNC(STORE.STORE/10),10,4,9,4,8,3,7,2,6,1,0) STORE,CLASS.CLASSNAME FROM STUDENT,STORE,CLASS WHERE STUDENT.STUDENTID=STORE.STUDENTID AND CLASS.CLASSID=STORE.CLASSID) S 该怎么改
select s.studentid,s.name,k.store,c.calssname, decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) xf, sum(decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY s.studentid) zf from student s,store k,class c where s.studentid=k.studentid(+) and c.classid=k.classid 用左连接试下看看 Oracle的左连接和右连接 在Oracle PL-SQL中,左连接和右连接以如下方式来实现 查看如下语句: SELECT emp_name, dept_name FORM Employee, Department WHERE Employee.emp_deptid(+) = Department.deptid此SQL文使用了右连接,即“(+)”所在位置的另一侧为连接的方向,右连接说明等号右侧的所有记录均会被显示,无论其在左侧是否得到匹配,也就是说上例中无论会不会出现某个部门没有一个员工的情况,这个部门的名字都会在查询结果中出现。反之: SELECT emp_name, dept_name FORM Employee, Department WHERE Employee.emp_deptid = Department.deptid(+)则是左连接,无论这个员工有没有一个能在Department表中得到匹配的部门号,这个员工的记录都会被显示
试过了 不好用 不知道怎么回事 能帮着解决吗>>>??? 外连接我知道!!!! 谢谢了
select s.studentid,s.name,p.store,p.classid,p.calssname, decode(Trunc(p.store/10, 0),10,4,9,4,8,3,7,2,6,1,0) xf, sum(decode(Trunc(p.store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY p.studentid) zf from student s,(select k.*,c.calssnamefrom store k,class c where c.classid=k.classid) p where s.studentid=p.studentid(+)
B.结贴,按“管理“就知道了
decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) 学分,
sum(store) over (PARTITION BY s.studentid) 总分
(store-50)%10 学分 from student s,store k,class c
where s.studentid=k.studentid and c.classid=k.classid
select s.studentid 学号,s.name 姓名,k.store 分数,c.calssname 课程名,
decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) 学分,
sum(store) over (PARTITION BY s.studentid) 总分
from student s,store k,class c
where s.studentid=k.studentid and c.classid=k.classid
这就去解贴
我去管理那了
也出来确认对话框了
可是就是没有给分
出现这个
http://community.csdn.net/expert/GiveTip.asp
我看不懂内容我是日文系统
`1`1`
从容,你的分我会给的 不好意思 ~!!! 还没弄明白
不行去网吧弄
select s.studentid,s.name,k.store,c.calssname,
decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) xf,
sum(decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY s.studentid) zf
from student s,store k,class c
where s.studentid=k.studentid and c.classid=k.classidover 是Oracle的分析函数
sum(decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY s.studentid) 表示按照学号对学分进行分区统计得到总学分
例如总学分查询结果如下:
学号 学分 总学分
1 1 3
1 2 3
2 2 5
2 3 5附录:
over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数
over(partition by deptno)按照部门分区
over(order by salary range between 50 preceding and 150
following)
每行对应的数据窗口是之前行幅度值不超过50,之后行幅度值不超过150
over(order by salary rows between 50 preceding and 150
following)
每行对应的数据窗口是之前50行,之后150行
over(order by salary rows between unbounded preceding
and unbounded following)
每行对应的数据窗口是从第一行到最后一行,等效:
over(order by salary range between unbounded preceding
and unbounded following)
S.ID 學号,
S.NAME 姓名,
DECODE(S.CLASSNAME,'ENGLISH','ENGLISH') 課程1,
DECODE(S.CLASSNAME,'ENGLISH',S.STORE) 課程1學分,
DECODE(S.CLASSNAME,'MATH','MATH') 課程2,
DECODE(S.CLASSNAME,'MATH',S.STORE) 課程2學分,
DECODE(S.CLASSNAME,'OPERATION','OPERATION') 課程3,
DECODE(S.CLASSNAME,'OPERATION',S.STORE) 課程3學分,
DECODE(S.CLASSNAME,'CHINESE','CHINESE') 課程4,
DECODE(S.CLASSNAME,'CHINESE',S.STORE) 課程4學分,
DECODE(S.CLASSNAME,'DATABASE','DATABASE') 課程5,
DECODE(S.CLASSNAME,'DATABASE',S.STORE) 課程5學分
FROM
(SELECT STUDENT.NAME,(STUDENT.STUDENTID)AS ID,DECODE(TRUNC(STORE.STORE/10),10,4,9,4,8,3,7,2,6,1,0) STORE,CLASS.CLASSNAME
FROM STUDENT,STORE,CLASS
WHERE STUDENT.STUDENTID=STORE.STUDENTID AND CLASS.CLASSID=STORE.CLASSID) S
该怎么改
decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0) xf,
sum(decode(Trunc(store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY s.studentid) zf
from student s,store k,class c
where s.studentid=k.studentid(+) and c.classid=k.classid
用左连接试下看看
Oracle的左连接和右连接
在Oracle PL-SQL中,左连接和右连接以如下方式来实现 查看如下语句:
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee.emp_deptid(+) = Department.deptid此SQL文使用了右连接,即“(+)”所在位置的另一侧为连接的方向,右连接说明等号右侧的所有记录均会被显示,无论其在左侧是否得到匹配,也就是说上例中无论会不会出现某个部门没有一个员工的情况,这个部门的名字都会在查询结果中出现。反之:
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee.emp_deptid = Department.deptid(+)则是左连接,无论这个员工有没有一个能在Department表中得到匹配的部门号,这个员工的记录都会被显示
不好用
不知道怎么回事
能帮着解决吗>>>???
外连接我知道!!!!
谢谢了
decode(Trunc(p.store/10, 0),10,4,9,4,8,3,7,2,6,1,0) xf,
sum(decode(Trunc(p.store/10, 0),10,4,9,4,8,3,7,2,6,1,0)) over (PARTITION BY p.studentid) zf
from student s,(select k.*,c.calssnamefrom store k,class c where c.classid=k.classid) p
where s.studentid=p.studentid(+)