一个sql语句

Question

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

Answer 1

在我的Blog上面有一篇我的文章,可以参考以下:http://blog.csdn.net/dinya2003/archive/2004/11/30/198816.aspx

Answer 2

SQL> select * from student;CODE                 NAME                 SUBJECT                                      MARKING
-------------------- -------------------- ---------------------------------------- -----------
01                   小张                 语文                                              70
01                   小张                 数学                                              80
02                   小明                 语文                                              90
02                   小明                 数学                                              60SQL> 
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                            300SQL>

Answer 3

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        300SQL>

Answer 4

看错了.我以为是累计.
这样就行了.
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