关于表中多行数据编号的问题

[birdvirgo]

问题是这样的：有一个表student，有3列：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 NAME SCORE COUSE SUM(SCORE) NUM
1 JIM 60 MATH 170 1
1 JIM 70 ENGLISH 170 2
1 JIM 40 CHINESE 170 3
2 TOM 89 MATH 169 1
2 TOM 80 CHINESE 169 2

请各位大侠帮小弟解决一下，谢谢了

[心中的彩虹]

[Quote=引用楼主 birdvirgo 的回复:]
问题是这样的：有一个表student，有3列：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表中的数据全给取出来，并且求出一个人的总分数……
[/Quote]

select ID,NAME,SCORE,COUSE,SUM(SCORE) OVER(PARTITION BY NAME ORDER BY ID) SUM_SCORE,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY COUSE) NUM
FROM student

[真的不知道取什么名]

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(score) over(partition by id) sumscore,
       row_number() over(partition by id order by couse) rn
from tb

       ID NAM      SCORE COUSE     SUMSCORE         RN
--------- --- ---------- ------- ---------- ----------
        1 JIM         40 CHINESE        170          1
        1 JIM         70 ENGLISH        170          2
        1 JIM         60 MATH           170          3
        2 TOM         80 CHINESE        169          1
        2 TOM         89 MATH           169          2

[lzbbob1985]

select t.*,sum(SCORE) over(partition by ID,NAME ),
sum(1) over(partition by ID, name ,order by SCORE desc ) from student t

[lzbbob1985]

上面的有问题

这个

select t.*,sum(SCORE) over(partition by ID,NAME ),
sum(1) over(partition by ID, name order by SCORE desc ) from student t 
 

[lxyzxq2008]

with tab as(
 select 1 as ID, 'JIM' as NAME, 60 as SCORE, 'MATH' as COUSE from dual union all
 select 1 as ID, 'JIM' as NAME, 70 as SCORE, 'ENGLISH' as COUSE from dual union all
 select 1 as ID, 'JIM' as NAME, 40 as SCORE, 'CHINESE' as COUSE from dual union all
 select 2 as ID, 'TOM' as NAME, 89 as SCORE, 'MATH' as COUSE from dual union all
 select 2 as ID, 'TOM' as NAME, 80 as SCORE, 'CHINESE' as COUSE from dual
)
select id,name,SCORE, couse, sum(score) over(PARTITION by name order by ID) SUM_SCORE, row_number() over(PARTITION by name order by id) from tab
--------------------------------------------------------------------
1	JIM	60	MATH	170	1
1	JIM	70	ENGLISH	170	2
1	JIM	40	CHINESE	170	3
2	TOM	89	MATH	169	1
2	TOM	80	CHINESE	169	2

[njlywy]

select id,name,score,couse,sum(score) over(partition by id) sumscore,
row_number() over(partition by id order by couse) rn
from tablename;