求教一条sql语句

[Vicar2]

刚才看帖子，只会sql server的写法，不会oracle的写法.请教如下语句如何翻译成oracle的，多谢！
(科目数量不固定)

--成绩表
Create table score
(
student_id varchar(10),      ---学号 
grade varchar(10),  ---年级 
subjects varchar(20), ----科目 
score int           ----分数 
)
--分数表
Insert Into Score
Select '0001', '1', '语文', 70 union
Select '0001', '1', '数学', 90 union
Select '0001', '1', '英语', 89 union
Select '0002', '1', '语文', 70 union
Select '0002', '1', '数学', 93 union
Select '0002', '1', '英语', 78 union
Select '0003', '1', '语文', 80 union
Select '0003', '1', '数学', 90 union
Select '0003', '1', '英语', 83 

/*
以下动态构建如下sql
Select 
  student_id,
  grade,
  max(case subjects when '语文' then score else 0 end) 语文,
  max(case subjects when '英语' then score else 0 end) 英语,
  max(case subjects when '数学' then score else 0 end) 数学
from score
group by student_id, grade
*/

declare @sSql varchar(8000)
Set @sSql = '';
Select @sSql = @sSql +  'max(case subjects when ''' + subjects + ''' then score else 0 end) ''' + subjects + ''',' from 
(
    Select distinct subjects from score
) t
Set @sSql = substring(@sSql, 1, len(@sSql) - 1)

Set @sSql = 
'Select ' +
'  student_id, ' +
'  grade, ' +
@sSql +
' from score ' +
' group by student_id, grade '

exec(@sSql)

--输出结果
/*
student_id grade 数学 英语 语文
0001	1	90	89	70
0002	1	93	78	70
0003	1	90	83	80
*/

--附：Oracle的件表sql如下

Create table Score
(
student_id varchar(10),      ---学号 
grade varchar(10),  ---年级 
subjects varchar(20), ----科目 
score int           ----分数 
);

Insert into Score
Select '0001', '1', '语文', 70 from dual union
Select '0001', '1', '数学', 90 from dual union
Select '0001', '1', '英语', 89 from dual union
Select '0002', '1', '语文', 70 from dual union
Select '0002', '1', '数学', 93 from dual union
Select '0002', '1', '英语', 78 from dual union
Select '0003', '1', '语文', 80 from dual union
Select '0003', '1', '数学', 90 from dual union
Select '0003', '1', '英语', 83 from dual 

[zuzuou]

教你一个笨一点，但是效率会比写过程要高很多的办法：
执行以下语句，再把结果拷贝出来直接执行~~oyear~
select 'Select ' from dual
union all
select 'max(case subjects when '||''''||subjects ||''''||' then score else 0 end) as '|| subjects||','
from (
Select distinct subjects from score
) t
union all
select 'student_id, grade' from dual
union all
select 'from score' from dual
union all
select 'group by student_id, grade' from dual

[dawugui]

动态的不会,静态和sql几乎一样.

[flush_520]

sSql := sSql||' max(case subjects when '||subjects||' then score else 0 end) '||subjects||','
少了个分号，改为
sSql := sSql||' max(case subjects when '||subjects||' then score else 0 end) '||subjects||',';

[forplay]

又被你拿到分了,老子日

[Linux_9]

[Quote=引用 2 楼 dawugui 的回复:]
动态的不会,静态和sql几乎一样.
[/Quote]
哈哈，又见大乌龟。

[inthirties]

SQL>select student_id , grade , sum(decode(subjects, '数学', score, 0)) 数学, sum(decode(subjects, '英语', score, 0)) 英语,sum(decode(subjects, '语文', score, 0)) 语文 from score group by student_id, grade order by student_id;

STUDENT_ID GRADE 数学 英语 语文
---------- ---------- ---------- ---------- ----------
0001 1 90 89 70
0002 1 93 78 70
0003 1 90 83 80

[inthirties]

[Quote=引用 6 楼 inthirties 的回复:]
SQL>select student_id , grade , sum(decode(subjects, '数学', score, 0)) 数学, sum(decode(subjects, '英语', score, 0)) 英语,sum(decode(subjects, '语文', score, 0)) 语文 from score group by student_id, grade order by student_id;

STUDENT_ID GRADE            数学      英语      语文
---------- ---------- ---------- ---------- ----------
0001      1                  90        89        70
0002  …
[/Quote]

如果你这里不仅仅只有数学，英语，语文科目的话，可以拼成类似这样的sql来执行。

[welyngj]

这个是动态的。

[flush_520]

DECLARE
        TYPE t_cursor IS ref CURSOR;
        v_cursor       t_cursor;
        subjects       varchar2(20);
				sSql varchar2(8000);
				vSql varchar2(8000);
BEGIN
       sSql :='';
        --------打开游标 对sSql附case语句
        OPEN v_cursor FOR select distinct subjects  from score ;
         loop
           fetch v_cursor into subjects;
           EXIT WHEN v_cursor%NOTFOUND;
              sSql := sSql||' max(case subjects when '||subjects||' then score else 0 end) '||subjects||','
         end loop;
         CLOSE v_cursor;
				 sSql := substr(sSql, 1, length(sSql)-1);
			-----执行sql
      vSql :='Select student_id,grade,'||sSql||' from score group by student_id, grade';
			
			    execute immediate vSql;
      commit;

EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;
END ;

调试一下

[welyngj]

这个帖子已经做出来了：

http://topic.csdn.net/u/20090627/10/f1d34c6d-c2fa-4db0-8ace-9253b1484324.html