select 班级,姓名,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by 班级,姓名
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by 班级,姓名
return grade
else
return nullsum()是将()内的数值累加
insert into user_score values('135','南岸','math','87');
insert into user_score values('135','南岸','english','93');
insert into user_score values('135','哈纳哈','english','77');
insert into user_score values('135','哈纳哈','math','66');select class,name,decode(subject,'math', grade,null),
decode(subject,'english', grade,null)
from user_score后面加 group by 就出错?
谁能帮我测试下,见表(没建主见)和数据上面都有了
sum(decode(subject,'english', to_number(grade),0))
from user_score
group by class,name;
据我所知,只有order by可以用别名。
(
select distinct 班级,姓名,班级||姓名 id from 表
) a,
(
select 班级,姓名,成绩,班级||姓名 id from 表 where 科目='语文'
) b,
(
select 班级,姓名,成绩,班级||姓名 id from 表 where 科目='数学'
) c,
(
select 班级,姓名,成绩,班级||姓名 id from 表 where 科目='外语'
) d
where a.id=b.id(+) and a.id=c.id(+) and a.id=d.id(+);
(
select distinct 班级,姓名,班级||姓名 id from 表
) a,
(
select 班级,姓名,成绩 语文,班级||姓名 id from 表 where 科目='语文'
) b,
(
select 班级,姓名,成绩 数学,班级||姓名 id from 表 where 科目='数学'
) c,
(
select 班级,姓名,成绩 外语,班级||姓名 id from 表 where 科目='外语'
) d
where a.id=b.id(+) and a.id=c.id(+) and a.id=d.id(+);
from
(select class,name,decode(subject,数学, grade,null) as 数学
from table1
) a,
(select class,name,decode(subject,'外语', grade,null)as 外语
from table1
) b,
(select class,name,decode(subject,'语文', grade,null) as 语文
from table1
) c
where a.class=b.class and a.class=c.class
and a.name=b.name and a.name=c.name
qiaozhiwei(乔)的方法如果列动态的话.我认为还是写过程好些.那样很灵活,不用去更改前台的程序代码了.
nanci 的方法,会有很多雍余数据出来,