刚才看帖子,只会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
(科目数量不固定)--成绩表
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
执行以下语句,再把结果拷贝出来直接执行~~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
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 ;
调试一下
少了个分号,改为
sSql := sSql||' max(case subjects when '||subjects||' then score else 0 end) '||subjects||',';
---------- ---------- ---------- ---------- ----------
0001 1 90 89 70
0002 1 93 78 70
0003 1 90 83 80
如果你这里不仅仅只有数学,英语,语文科目的话,可以拼成类似这样的sql来执行。