一个sql语句

[yuwu_zjl]

create table student(
code varchar(20),
name varchar(20),
subject varchar(40),
marking float
)

)
向你请教一个问题：
编码　名称　科目　　成绩　　
01　　小张　语文　　　70　　
01　　小张　数学　　　80
02　　小明　语文　　　90
02　　小明　数学　　　60
.........
对于这个表用SQL语句如何得到
编码　名称 语文 数学 总分
01 小张 70 80 150
02 小明 90 60 150

[liuyi8903]

SQL> select * from student;

CODE NAME SUBJECT MARKING
-------------------- -------------------- ---------------------------------------- -----------
01 小张 语文 70
01 小张 数学 80
02 小明 语文 90
02 小明 数学 60

SQL>
SQL> SELECT CODE,
2 SUM(DECODE(SUBJECT, '语文', MARKING, 0)) AS 语文,
3 SUM(DECODE(SUBJECT, '数学', MARKING, 0)) AS 数学,
4 SUM(SUM(MARKING)) OVER(ORDER BY CODE)
5 FROM STUDENT
6 GROUP BY CODE
7 /

CODE 语文 数学 SUM(SUM(MARKING))OVER(ORDERBYC
-------------------- ---------- ---------- ------------------------------
01 70 80 150
02 90 60 300

SQL>

[liuyi8903]

SQL> SELECT CODE,
2 NAME,
3 SUM(DECODE(SUBJECT, '语文', MARKING, 0)) AS 语文,
4 SUM(DECODE(SUBJECT, '数学', MARKING, 0)) AS 数学,
5 SUM(SUM(MARKING)) OVER(ORDER BY CODE) AS 总分
6 FROM STUDENT
7 GROUP BY CODE, NAME
8 /

CODE NAME 语文 数学 总分
-------------------- -------------------- ---------- ---------- ----------
01 小张 70 80 150
02 小明 90 60 300

SQL>

[liuyi8903]

看错了.我以为是累计.
这样就行了.
SELECT CODE,
NAME,
SUM(DECODE(SUBJECT, '语文', MARKING, 0)) AS 语文,
SUM(DECODE(SUBJECT, '数学', MARKING, 0)) AS 数学,
-- SUM(SUM(MARKING)) OVER(ORDER BY CODE ROWS 1 preceding) AS 总分
SUM(MARKING) 总分
FROM STUDENT
GROUP BY CODE, NAME

[batt]

select code,name,sum(decode(subject,'数学',marking,0)),sum(decode(subject,'语文',marking,0)),sum(marking)
from student
group by code,name

[dinya2003]

在我的Blog上面有一篇我的文章,可以参考以下:

http://blog.csdn.net/dinya2003/archive/2004/11/30/198816.aspx