问题是这样的:有一个表student,有4列:ID,NAME,SCORE,COUSE
如 ID NAME SCORE COUSE
1 JIM 60 MATH
1 JIM 70 ENGLISH
1 JIM 40 CHINESE
2 TOM 89 MATH
2 TOM 80 CHINESE我的问题:把student表中的数据全给取出来,并且求出一个人(具有相同ID)的单科分数大于或等于69分的总分数(小于60的不计算),可能表达不清楚,给出想要的结果。ID NAM SCORE COUSE SUMSCORE
--------- --- ---------- ------- ----------
1 JIM 60 MATH 130
1 JIM 70 ENGLISH 130
1 JIM 40 CHINESE 130
2 TOM 80 MATH 169
2 TOM 89 CHINESE 169
如 ID NAME SCORE COUSE
1 JIM 60 MATH
1 JIM 70 ENGLISH
1 JIM 40 CHINESE
2 TOM 89 MATH
2 TOM 80 CHINESE我的问题:把student表中的数据全给取出来,并且求出一个人(具有相同ID)的单科分数大于或等于69分的总分数(小于60的不计算),可能表达不清楚,给出想要的结果。ID NAM SCORE COUSE SUMSCORE
--------- --- ---------- ------- ----------
1 JIM 60 MATH 130
1 JIM 70 ENGLISH 130
1 JIM 40 CHINESE 130
2 TOM 80 MATH 169
2 TOM 89 CHINESE 169
解决方案 »
- 数据库应配置日志功能,对用户登录进行记
- 通过SQL语句如何获取某个表中某字段内容的最大长度
- 关于join的问题
- 两种SQL语句为什么执行速度相差那么大呢?请给出根本的答案
- Oracle数据导入问题,请教!
- ******如何比较两个字段的值,然后返回一个结果值?在线等待..******
- 关于ORACLE INDEX的问题,高手请进!
- 小弟最近想学oracle,请大家留下qq~~~
- Oracle数据导入....朋友们帮帮..
- 各位高手,自增长字段没做成功!
- 为什么我在安装完oracle 10g 后能用pl/sql连接orcale,重启电脑后就不行了?
- oracle的全表扫描为啥这么慢呢?是否信春哥,有这么重要吗?
with tb as(
select 1 ID,'JIM' NAME,60 SCORE,'MATH' COUSE from dual union all
select 1,'JIM',70,'ENGLISH' from dual union all
select 1,'JIM',40,'CHINESE' from dual union all
select 2,'TOM',89,'MATH' from dual union all
select 2,'TOM',80,'CHINESE' from dual)
--以上为提供数据的语句
select id,name,score,couse,
sum(case when score>=60 then score else 0 end) over (partition by id) cnt
from tb ID NAM SCORE COUSE CNT
---------- --- ---------- ------- ----------
1 JIM 60 MATH 130
1 JIM 70 ENGLISH 130
1 JIM 40 CHINESE 130
2 TOM 89 MATH 169
2 TOM 80 CHINESE 169
SQL> WITH TB AS
2 (SELECT '1' ID,'JIM' NAME,'60' SCORE ,'MATH'COUSE FROM dual
3 UNION ALL
4 SELECT '1', 'JIM', '70', 'ENGLISH'
5 FROM dual
6 UNION ALL
7 SELECT '1', 'JIM', '40', 'CHINESE' FROM dual
8 union all
9 select '2','TOM','89','MATH' from dual
10 union all
11 select '2','TOM','80','CHINESE' from dual
12 )
13 SELECT T.ID,T.NAME,T.SCORE,T.COUSE,
14 SUM(CASE WHEN T.SCORE>='60' THEN T.SCORE END) OVER(PARTITION BY T.ID) SUMSCORE FROM TB T;I NAM SC COUSE SUMSCORE
- --- -- ------- ----------
1 JIM 60 MATH 130
1 JIM 70 ENGLISH 130
1 JIM 40 CHINESE 130
2 TOM 89 MATH 169
2 TOM 80 CHINESE 169
sum(decode(sign(SCORE-60),1,SCORE,0,SCORE,0)) over(partition by id order by score) SUMSCORE
from student
from Student;
如有重复行,如多了一行(1 jim 60 math ),请问要怎么在原来问题中消除计算重复行的问题?谢谢
SELECT T.ID,T.NAME,T.SCORE,T.COUSE,
SUM(CASE WHEN T.SCORE>='60' THEN T.SCORE END) OVER(PARTITION BY T.ID) SUMSCORE
FROM (select distinct * from tb) T
select ID ,NAME,SCORE,COUSE,
sum(decode(sign(SCORE-60),1,SCORE,0,SCORE,0)) over(partition by id order by score) SUMSCORE
from (select distinct ID ,NAME,SCORE,COUSE from student)
SUM(CASE WHEN SCORE>=60 THEN SCORE ELSE 0 END) OVER(PARTITION BY ID) "SUMSCORE"
WHERE (
SELECT DISTINCT ID, NAME, SCORE, COURSE FROM Student
);