有一成绩表(学生编号,科目名称,分数):
create table score(studentNo int,subject char(4),score)
现在查询所有学生成绩,要求显示格式是:编号 语文 数学 英语
-----------------------
1 90 89 95
2 85 75 null
3 92 95 85科目名称未知.
请教各位大大,帮忙解决
create table score(studentNo int,subject char(4),score)
现在查询所有学生成绩,要求显示格式是:编号 语文 数学 英语
-----------------------
1 90 89 95
2 85 75 null
3 92 95 85科目名称未知.
请教各位大大,帮忙解决
select names, sum(decode(course,'语文', grade,null)) "语文",
sum(decode(course,'数学', grade,null)) "数学",
sum(decode(course,'英语', grade,null)) "英语"
from tmp
group by names
不过这样好象不通用,decode是Oracle才有的函数.
我现在已经有解法了
select studentNo, chinese 语文,math 数学, eng 英语
from (select score chinese, 0 math, 0 eng where subject = '语文'
union
select 0 chinese, score math, 0 eng where subject = '数学'
union
select 0 chinese, 0 math, score eng where subject = '英语') as a
group by studentNo
这样似乎更好一点...
SQL> select a.id,a.sore as "yumen",b.sore as "shuxue",c.sore as "yingyu" from chengji a,chengji b,chengji c
2 where a.id=b.id
3 and c.id=b.id
4 and a.source='yuwen'
5 and b.source='shuxue'
6 and c.source='yingyu'; ID yumen shuxue yingyu
---------- -------- -------- --------
1 98 85 82
2 96 52 84
3 41 77 96
4 82 82 98