给你一个例子:表名 s_grade 假设表的结构 ID VARCHAR2(10) --学号 NAME VARCHAR2(10) --姓名 SUBJECT VARCHAR2(10) --科目 GRADE NUMBER --成绩 表类数据 NAME SUBJECT GRADE ID ---------- ---------- --------- ---------- qq1 数学 90 001 qq2 数学 91 002 qq3 数学 92 003 qq1 语文 80 001 qq2 语文 82 002 qq3 语文 89 003 qq2 外语 75 002 qq3 外语 70 003 qq1 外语 78 001 sql 语句 select a.id,a.name, nvl(语文,0) 语文, nvl(数学,0) 数学, nvl(外语,0) 外语, nvl(sum,0) sum from (select distinct id,name from s_grade) a, (select id,name, grade as 语文 from s_grade where subject='语文') b , (select id,name, grade as 数学 from s_grade where subject='数学') c , (select id,name, grade as 外语 from s_grade where subject='外语') d , (select id,name, sum(grade) sum from s_grade group by id,name) e where a.id = b.id(+) and a.id = c.id(+) and a.id = d.id(+) and a.id = e.id(+); 结果 ID NAME 语文 数学 外语 SUM ---------- ---------- --------- --------- --------- --------- 001 qq1 80 90 78 248 002 qq2 82 91 75 248 003 qq3 89 92 70 251
select AA.*,(AA.eee+AA.efe+AA.eke) as total from ( SELECT id, SUM(CASE xm WHEN 'eee' THEN je ELSE 0 END) AS eee, SUM(CASE xm WHEN 'efe' THEN je ELSE 0 END) AS efe, SUM(CASE xm WHEN 'eke' THEN je ELSE 0 END) AS eke FROM table1 GROUP BY id ) as AA
ID VARCHAR2(10) --学号
NAME VARCHAR2(10) --姓名
SUBJECT VARCHAR2(10) --科目
GRADE NUMBER --成绩 表类数据
NAME SUBJECT GRADE ID
---------- ---------- --------- ----------
qq1 数学 90 001
qq2 数学 91 002
qq3 数学 92 003
qq1 语文 80 001
qq2 语文 82 002
qq3 语文 89 003
qq2 外语 75 002
qq3 外语 70 003
qq1 外语 78 001 sql 语句 select a.id,a.name,
nvl(语文,0) 语文,
nvl(数学,0) 数学,
nvl(外语,0) 外语,
nvl(sum,0) sum
from (select distinct id,name from s_grade) a,
(select id,name, grade as 语文 from s_grade where subject='语文') b ,
(select id,name, grade as 数学 from s_grade where subject='数学') c ,
(select id,name, grade as 外语 from s_grade where subject='外语') d ,
(select id,name, sum(grade) sum from s_grade group by id,name) e
where a.id = b.id(+)
and a.id = c.id(+)
and a.id = d.id(+)
and a.id = e.id(+); 结果
ID NAME 语文 数学 外语 SUM
---------- ---------- --------- --------- --------- ---------
001 qq1 80 90 78 248
002 qq2 82 91 75 248
003 qq3 89 92 70 251
from
(
SELECT id,
SUM(CASE xm WHEN 'eee' THEN je ELSE 0 END) AS eee,
SUM(CASE xm WHEN 'efe' THEN je ELSE 0 END) AS efe,
SUM(CASE xm WHEN 'eke' THEN je ELSE 0 END) AS eke
FROM table1
GROUP BY id
) as AA
http://www.csdn.net/Expert/topic/508/508264.shtm