首页 新闻 会员 周边 捐助

oracle 如何根据枚举生成多条记录。

0
悬赏园豆：5 [已解决问题] 解决于 2015-12-23 10:18

我也不知道如何描述问题。截图如下

A表，科目信息表（有限的科目，可以枚举出来）

B表，学生成绩表（科目在A表中）

C表，需要的结果

 

如上，要求是对于A表的各个科目。如果B表有成绩记录，就生成一条记录。

如果B表中没有的话，就在C表中新生成一条记录，成绩为0

求Sql语句。

问题补充：

在此提供A表、B表的sql语句。

A表

with 
TA as
(
select '英语' as course from dual union all
select '数学' as course from dual union all
select '语文' as course from dual 
)select * from TA

B表

with
TB as
(
   select 'Rex' as name,89 as grade,'英语' as course from dual union all
   select 'Rex' as name,90 as grade,'语文' as course from dual union all
   select 'Tom' as name,67 as grade,'语文' as course from dual union all
   select 'Jack' as name,56 as grade,'语文' as course from dual union all
   select 'Jack' as name,88 as grade,'数学' as course from dual union all
   select 'Jack' as name,97 as grade,'英语' as course from dual 
)select * from TB
Gamain的主页 Gamain | 菜鸟二级 | 园豆：357
提问于：2013-10-14 16:52
< >
分享
最佳答案
0

自己解决了。

如下：

with 
TA as
(
   select '英语' as course from dual union all
   select '数学' as course from dual union all
   select '语文' as course from dual 
),
TB as
(
   select 'Rex' as name,89 as grade,'英语' as course from dual union all
   select 'Rex' as name,90 as grade,'语文' as course from dual union all
   select 'Tom' as name,67 as grade,'语文' as course from dual union all
   select 'Jack' as name,56 as grade,'语文' as course from dual union all
   select 'Jack' as name,88 as grade,'数学' as course from dual union all
   select 'Jack' as name,97 as grade,'英语' as course from dual 
)
select 
TB.name,sum(decode(TB.course,TA.course,TB.grade,0)) as grade,TA.course
from TA ,TB 
group by
TB.name,TA.course
order by TB.name,TA.course
Gamain | 菜鸟二级 |园豆：357 | 2013-10-14 17:04
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。