declare @sql varchar(8000)
set @sql = 'select a.sid 学号,a.sname 姓名 '
select @sql = @sql + ' , max(case c.cname when ''' + c.cname + ''' then sc.score else 0 end) [' + c.cname + ']'
from (select distinct c.cname from course as c) as c
set @sql = @sql + ' from student a inner join sc sc on sc.sid=a.sid
inner join course c on c.cid=sc.cid group by a.sid,a.sname'
exec(@sql)
如何把server sql动态语句转Oracle ??
set @sql = 'select a.sid 学号,a.sname 姓名 '
select @sql = @sql + ' , max(case c.cname when ''' + c.cname + ''' then sc.score else 0 end) [' + c.cname + ']'
from (select distinct c.cname from course as c) as c
set @sql = @sql + ' from student a inner join sc sc on sc.sid=a.sid
inner join course c on c.cid=sc.cid group by a.sid,a.sname'
exec(@sql)
如何把server sql动态语句转Oracle ??
http://topic.csdn.net/u/20091018/15/7c6729b6-79d1-492e-862d-af9c4921f172.html?91738表结构:
union sex
004 2
002 2
002 2
003 1
002 1 现在我想显示这样的结果
1 2
002 1 2
003 1 0
004 0 1
也就是说按union sex两个字段count人数,得到二维表
--创建过程
create or replace procedure proc
as
sqlstr varchar2(2000):='create or replace view v_tmp as select "UNION"';
begin
for cur1 in (select distinct sex from tab1)
loop
sqlstr:=sqlstr||'
,count(decode(sex,'||cur1.sex||',1))"'||cur1.sex||'"';
end loop;
sqlstr:=sqlstr||' from tab1 group by "UNION"';
execute immediate sqlstr;
end;
--调用
begin
proc;
end;
--查看结果
select * from v_tmp
exec()函数换成 execute immediate
insert into course
select 1,'zhang','语文',100 from dual union all
select 1,'zhang','数学',90 from dual union all
select 1,'zhang','化学',80 from dual union all
select 3,'wang','语文',100 from dual union all
select 3,'wang','数学',100 from dual union all
select 3,'wang','化学',70 from dual;--动态
declare
V_sql varchar2(2000);
cursor cr_trans is select distinct cname from course;
begin
for CR IN cr_trans loop
V_sql:=V_sql||',SUM(decode(cname,'''||CR.cname||''',score,0)) '||CR.cname||'';
end loop;
V_sql:='create or replace view v_tmp as select sid,sname,'||SUBSTR(V_sql,2)||' from course GROUP BY sid,sname';
DBMS_OUTPUT.PUT_LINE(V_sql);
execute immediate V_sql;--执行阶段
end ;select *from v_tmp
--静态
select sid,sname,SUM(decode(cname,'语文',score,0))语文,
SUM(decode(cname,'数学',score,0))数学,
SUM(decode(cname,'化学',score,0))化学
from course GROUP BY sid,sname
set @sql = 'select a.sid 学号,a.sname 姓名 '
select @sql = @sql + ' , max(case c.cname when ''' + c.cname + ''' then sc.score else 0 end) [' + c.cname + ']'
from (select distinct c.cname from course as c) as c
set @sql = @sql + ' from student a inner join sc sc on sc.sid=a.sid
inner join course c on c.cid=sc.cid group by a.sid,a.sname'
exec(@sql)用这个写成Oracle 语句啊,其他的看不懂
create or replace procedure get_score_stats (stats_cur out sys_refcursor)
is
stmt varchar2(4000):='select st.sid [学号], st.sname [姓名]';
begin
for c in (select distinct cname from course) loop
stmt:=stmt||',max(decode(c.cname,'''||c.cname||''',s.score,0)) ['||c.cname||']';
end loop; stmt:=stmt||' from student st, sc s,course c where st.sid=s.sid and c.cid=s.cid group by st.sid, st.sname'; open stats_cur for stmt;
end;
/
exec 存储过程名字 这样都不出来结果的???????????????????