一道Oracle面试题,急求解,一起讨论啊？ 要求通用,可以继续加分！

Question

Create Table a  --代表明星姓名
(
Acode varchar(10), 
Aname varchar(20)
);
Create Table b  --代表明星去过地方
(
Bcode varchar(10), 
Baddr varchar(20)
);Create Table main --去过次数主表
(
ACode varchar(10),
BCode varchar(10),
FCount number
);insert into a values('01','刘德华');
insert into a values('02','张惠妹');
insert into a values('03','周杰伦');
insert into a values('04','孙燕姿');insert into b values('001','台湾');
insert into b values('002','内地');
insert into b values('003','香港');
insert into b values('004','美国');
insert into b values('005','英国');
insert into b values('006','法国');insert into main values('01','001',30);
insert into main values('01','002',120);
insert into main values('01','004',110);
insert into main values('02','003',27);
insert into main values('03','005',23);
insert into main values('03','003',15);
insert into main values('04','002',102);commit;Select * from main;需要得到的数据为:姓名      台湾   内地  香港   美国   法国  合计
------------------------------------------
刘德华    30    120    0      110    0     260       
张惠妹    0      0     27     0      0     27
周杰伦    0      0     15     0      23    38
孙燕姿    0     102    0      0      0     102
------------------------------------------
合计      30    202    42     110    23    427

Answer 1

又是行列转换的问题，固定国家的用DECODE来做
不固定国家的用存储地程或者临时表来实现

Answer 2

我只会固定的
比一般的复杂，需要求合计，可以通过分析函数rollup或cube，我不会用
select a.Aname,
sum(decode(b.baddr,'台湾',m.FCount,0 )) as 台湾,
sum(decode(b.baddr,'内地',m.FCount,0 )) as 内地,
sum(decode(b.baddr,'香港',m.FCount,0 )) as 香港,
sum(decode(b.baddr,'美国',m.FCount,0 )) as 美国,
sum(decode(b.baddr,'英国',m.FCount,0 )) as 英国,
sum(decode(b.baddr,'法国',m.FCount,0 )) as 法国,
sum(m.FCount) as 合计
from a,main m,b
where a.Acode =m.Acode and b.bcode =m.bcode
group by a.aname
union all
select '',
sum(decode(b.baddr,'台湾',m.FCount,0 )) as 台湾,
sum(decode(b.baddr,'内地',m.FCount,0 )) as 内地,
sum(decode(b.baddr,'香港',m.FCount,0 )) as 香港,
sum(decode(b.baddr,'美国',m.FCount,0 )) as 美国,
sum(decode(b.baddr,'英国',m.FCount,0 )) as 英国,
sum(decode(b.baddr,'法国',m.FCount,0 )) as 法国,
sum(m.FCount) 
from a,main m,b
where a.Acode =m.Acode and b.bcode =m.bcode楼主要的是动态实现的，等人来吧！

Answer 3

刚在论坛搜索了下，有SQL_SERVER的解决方案
和你的例子很相似，zjcxc（邹建）老大回答的http://topic.csdn.net/t/20040712/15/3167402.html---------------------------------------------------------------
Create   table   test   (name   char(10),km   char(10),cj   int)   
  go   
  insert   test   values('张三','语文',80)   
  insert   test   values('张三','数学',86)   
  insert   test   values('张三','英语',75)   
  insert   test   values('李四','语文',78)   
  insert   test   values('李四','数学',85)   
  insert   test   values('李四','英语',78)   
  想得到：   
  姓名       语文       数学       英语   
  张三       80           86           75   
  李四       78           85           78   
    
  我并不是想得到以上的结果，我想得到的是以下的结果：   
  姓名               语文       数学       英语       小计（此字段是张三的总分）   
  张三               80           86           75           241   
  李四               78           85           78           241   
  全班总分       158         171         153         482   
  全班平均       79           85.5       76.5       241   
--测试数据   
  Create   table   test   (name   char(10),km   char(10),cj   int)   
  insert   test   values('张三','语文',80)   
  insert   test   values('张三','数学',86)   
  insert   test   values('张三','英语',75)   
  insert   test   values('李四','语文',78)   
  insert   test   values('李四','数学',85)   
  insert   test   values('李四','英语',77)   
    
  --查询   
  declare   @sql   varchar(8000),@s1   varchar(8000)   
  select   @sql   =   '',@s1=''   
    
  select   @sql   =   @sql+   ',['+km+']=sum(case   km   when   '''+km+'''   then   cj   else   0   end)'   
  ,@s1=@s1+',sum(case   km   when   '''+km+'''   then   cj   else   0   end)/sum(case   km   when   '''+km+'''   then   1   else   0   end)'   
  from   test     
  group   by   km   
  exec('select   name=case   grouping(name)   when   1   then   ''全班总分''   else   name   end'+@sql+',小计=sum(cj)   
  from   test   
  group   by   name   with   rollup   
  union   all   
  select   ''全班平均分'''+@s1+',sum(cj)/count(distinct   name)   
  from   test')   
  go   
    
  --删除测试   
  drop   table   test   
    
  /*--测试结果   
    
  name               数学                     英语                     语文                     小计                       
  ----------   -----------   -----------   -----------   -----------     
  李四                   85                     77                     78                     240   
  张三                   86                     75                     80                     241   
  全班总分               171                   152                   158                   481   
  全班平均分             85                     76                     79                     240   
  --*/

Answer 4

Oracle实现方案 有动态的吗

Answer 5

--我这里只有SQL SERVER 动态的.
/*
普通行列转换(2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文　　74
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/-------------------------------------------------------------------------
/*
想变成 
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/create table tb
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql) 
/*
姓名         数学        物理        语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理,
  cast(avg(result*1.0) as decimal(18,2)) 平均分,
  sum(result) 总分
from tb
group by name
/*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1) 
/*
姓名         数学        物理        语文        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
张三         83          93          74          83.33                250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94想变成 
Name       Subject Result      
---------- ------- ----------- 
李四         语文      74
李四         数学      84
李四         物理      94
张三         语文      74
张三         数学      83
张三         物理      93
*/create table tb1
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name       Subject     Result               
---------- -------    -------------------- 
李四         语文      74.00
李四         数学      84.00
李四         物理      94.00
李四         平均分    84.00
李四         总分      252.00
张三         语文      74.00
张三         数学      83.00
张三         物理      93.00
张三         平均分    83.33
张三         总分      250.00
*/select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
  union all
  select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
  union all
  select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1

Answer 6

ORACLE的话,只有先静态的然后加上合计的(用union all)

Answer 7

--以下在SQL SERVER中实现.
Create Table a  --代表明星姓名
(
Acode varchar(10), 
Aname varchar(20)
);
Create Table b  --代表明星去过地方
(
Bcode varchar(10), 
Baddr varchar(20)
);Create Table main --去过次数主表
(
ACode varchar(10),
BCode varchar(10),
FCount int
);insert into a values('01','刘德华');
insert into a values('02','张惠妹');
insert into a values('03','周杰伦');
insert into a values('04','孙燕姿');insert into b values('001','台湾');
insert into b values('002','内地');
insert into b values('003','香港');
insert into b values('004','美国');
insert into b values('005','英国');
insert into b values('006','法国');insert into main values('01','001',30);
insert into main values('01','002',120);
insert into main values('01','004',110);
insert into main values('02','003',27);
insert into main values('03','005',23);
insert into main values('03','003',15);
insert into main values('04','002',102);select isnull(aname,'合计') 姓名 , 
  sum(case baddr when '台湾' then fcount else 0 end) '台湾',
  sum(case baddr when '内地' then fcount else 0 end) '内地',
  sum(case baddr when '香港' then fcount else 0 end) '香港',
  sum(case baddr when '美国' then fcount else 0 end) '美国',
  sum(case baddr when '法国' then fcount else 0 end) '法国',
  sum(fcount) '合计'
from
(
  Select A.aname , B.Baddr , C.Fcount from A , B , main C where C.Acode = A.Acode and C.Bcode = B.Bcode
) t
group by aname with rollupdrop table A,B,main/*
姓名                   台湾          内地          香港          美国          法国          合计
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
刘德华                  30          120         0           110         0           260
孙燕姿                  0           102         0           0           0           102
张惠妹                  0           0           27          0           0           27
周杰伦                  0           0           15          0           0           38
合计                   30          222         42          110         0           427(5 行受影响)
*/

Answer 8

我只会Sql Server通用的，希望有人能写出Oracle通用的
declare @str varchar(8000),@bcode varchar(20), @baddr varchar(20)
set @str = 'select isnull(aname,''合计'') ''姓名'' , '
declare cur cursor for select * from B
OPEN curFETCH NEXT FROM cur INTO @bcode, @baddrWHILE @@FETCH_STATUS = 0
begin
set @str = @str + 'sum(case baddr when '''+@baddr+''' then fcount else 0 end) '''+@baddr + ''','
FETCH NEXT FROM cur INTO @bcode, @baddr
end
CLOSE cur
DEALLOCATE curset @str = @str + 'sum(fcount) ''合计'' from (Select A.aname , B.Baddr , C.Fcount from A , B , main C 
where C.Acode = A.Acode and C.Bcode = B.Bcode) t group by aname with rollup'
exec (@str)

Answer 9

列的数目如果不固定的就只能通过游标返回罗按照2楼HelloWorld_001 的方法，通过存储过程循环地名拼接成一条动态语句，然后以游标形式返回结果集。

Answer 10

楼主看看这个declare
sqlstr varchar2(4000);
add b.baddr%type;
cursor cur is select distinct baddr from b; 
begin
sqlstr:='select a.Aname,';
open cur;
loop
fetch cur into add;
dbms_output.put_line('第'||cur%rowcount||'条:'||add);sqlstr:=sqlstr||'sum(decode(b.baddr,'||add||',m.FCount,0   ))   as   '||add||',';exit when cur%notfound;
end loop;sqlstr:=sqlstr||'sum(m.FCount) as 合计';
sqlstr:=sqlstr||'from a,main m,b';
sqlstr:=sqlstr||'where a.Acode=m.Acode and b.bcode =m.bcode';
sqlstr:=sqlstr||'group by a.aname';dbms_output.put_line(sqlstr);end;就是不知道怎么运行最后的sqlstr,谁能告诉一下?

Answer 11

执行结果为:
第1条:法国 
第2条:台湾 
第3条:香港 
第4条:内地 
第5条:美国 
第6条:英国 
第6条:英国 
select a.Aname,sum(decode(b.baddr,法国,m.FCount,0 )) as 法国,sum(decode(b.baddr,台湾,m.FCount,0 )) as 
台湾,sum(decode(b.baddr,香港,m.FCount,0 )) as 香港,sum(decode(b.baddr,内地,m.FCount,0 )) as 内地,sum(decode(b.baddr,美国,m.FCount,0 )) 
as 美国,sum(decode(b.baddr,英国,m.FCount,0 )) as 英国,sum(decode(b.baddr,英国,m.FCount,0 )) as 英国,sum(m.FCount) as 合计from a,main 
m,bwhere a.Acode=m.Acode and b.bcode =m.bcodegroup by a.aname

Answer 12

declare
sqlstr varchar2(4000);
add b.baddr%type;
v1 b.baddr%type;
v2 number;
v3 number;
v4 number;
v5 number;
v6 number;
v7 number;
v8 number;
cursor cur is select distinct baddr from b; 
type cur2_type is ref cursor;
cur2 cur2_type;
begin
sqlstr:='select a.Aname,';
open cur;
loop
fetch cur into add;
exit when cur%notfound;
dbms_output.put_line('第'||cur%rowcount||'条:'||add);sqlstr:=sqlstr||'sum(decode(b.baddr,'''||add||''',m.FCount,0   ))   as   '||add||',';
end loop;sqlstr:=sqlstr||'sum(m.FCount) as 合计 ';
sqlstr:=sqlstr||'from a,main m,b ';
sqlstr:=sqlstr||'where a.Acode=m.Acode and b.bcode =m.bcode ';
sqlstr:=sqlstr||'group by a.aname';dbms_output.put_line(sqlstr);open cur2 for sqlstr;
dbms_output.put_line('姓名   台湾  内地 香港 美国 法国 合计');
loop
fetch cur2 into v1,v2,v3,v4,v5,v6,v7,v8;   
exit when cur2 %notfound;  
dbms_output.put_line(v1||'  '||v2||'  '||v3||'  '||v4||'  '||v5||'  '||v6||'  '||v7||'  '||v8);   
end loop;  end;