有这样一个表:
student_id student_name Class Score
001 tony English 80
001 tony Maths 89
001 tony Chinese 90
002 Tom English 12
002 Tom Maths 34
002 Tom Chinese 19
003 Jerry English 100
003 Jerry Maths 89
003 Jerry Chinese 88
......
999 rich English 56
999 rich Maths 78
999 rich Chinese 90想得到如下结果:student_id student_name English Maths Chinese
001 tony 80 89 90
002 Tom 12 34 19
......
有什么好的办法没有?谢谢。在线等....
student_id student_name Class Score
001 tony English 80
001 tony Maths 89
001 tony Chinese 90
002 Tom English 12
002 Tom Maths 34
002 Tom Chinese 19
003 Jerry English 100
003 Jerry Maths 89
003 Jerry Chinese 88
......
999 rich English 56
999 rich Maths 78
999 rich Chinese 90想得到如下结果:student_id student_name English Maths Chinese
001 tony 80 89 90
002 Tom 12 34 19
......
有什么好的办法没有?谢谢。在线等....
解决方案 »
- 在单一的表中怎么提取相同名称的成员 菜菜鸟
- 请帮我补充
- 偌大一个csdn,竟然没人知道如何解决这个问题??!!
- oracle 建表,如何设置默认值 和 自增长列
- 查寻效率及索引方面请各位急救!
- oralce中能不建立一种自动忽略大小写的字段?
- 在线等一个非常小的问题,怎么在数据库中用sql语言增加字段,在线等,解决马上给分,只给唯一解决问题的人。
- 学习ORALE需要什么的预备知识
- oracle中是否有sql中的功能,跟踪程序,查找那些有问题的进程
- 急!!请教各位大侠,这个存储过程错在哪里?谢谢!
- 物化视图(Materialize View)与一般视图(View)有何区别
- 喜欢Oracle的进......
select distinct student_id,student_name,
decode(Class,English,Score,null) as English,
decode(Class,Maths,Score,null) as Maths,
decode(Class,Chinese,Score,null) as Chinese
from tablename
from
(
select student_id,student_name, Class,Score
from tablename
where Class='English'
) a,
(
select student_id,student_name, Class,Score
from tablename
where Class='Maths'
) b,
(
select student_id,student_name, Class,Score
from tablename
where Class='Chinese'
) c
where a.student_id=b.student_id and a.student_id=c.student_id
and a.student_name=b.student_name and a.student_name=c.student_name
行列转换的问题
少的话还是象前面那样写吧!不要考虑那么多
student_name,
sum(English) English,
sum(Maths) Maths,
sum(Chinese) Chinese
from (select student_id,
student_name,
Class,
decode(Class, 'English', Score, 0) English,
decode(Class, 'Maths', Score, 0) Maths,
decode(Class, 'Chinese', Score, 0) Chinese
from tablename) t
group by student_id, student_nameselect student_id,
student_name,
sum(English) English,
sum(Maths) Maths,
sum(Chinese) Chinese
from (select student_id,
student_name,
Class,
decode(Class, 'English', Score, 0) English,
decode(Class, 'Maths', Score, 0) Maths,
decode(Class, 'Chinese', Score, 0) Chinese
from tablename) t
group by student_id, student_name
student_name,
sum(English) English,
sum(Maths) Maths,
sum(Chinese) Chinese
from (select student_id,
student_name,
Class,
decode(Class, 'English', Score, 0) English,
decode(Class, 'Maths', Score, 0) Maths,
decode(Class, 'Chinese', Score, 0) Chinese
from tablename) t
group by student_id, student_name
FROM (
SELECT student_id, student_name, SCORE,
LAG(SCORE, 1, 0) OVER (PARTITION BY student_id ORDER BY student_id,CLASS) AS CHINESE,
SCORE AS ENGLISH,
LEAD(SCORE, 1, 0) OVER (PARTITION BY student_id ORDER BY student_id,CLASS) AS MATHS,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY student_id,CLASS) AS SEQ
FROM TBL_E
)
WHERE SEQ = 2避免了多次 SUM 及 GROUP BY 动作,效率 及 简洁性上要好一些
FROM (
SELECT student_id, student_name, FIRST_VALUE(SCORE) OVER (PARTITION BY ID ORDER BY ID,CLASS) AS CHINESE,
LAG(SCORE, 1,0) OVER (PARTITION BY student_id ORDER BY student_id,CLASS) AS ENGLISH,
SCORE AS MATHS,
LEAD(SCORE, 1,0) OVER (PARTITION BY student_id ORDER BY student_id,CLASS) AS MUSIC,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY student_id,CLASS) AS SEQ
FROM TBL_E
)
WHERE SEQ = 3;
如果栏位是可变的....呃...
.......你还是写Dynamic SQL吧:)
oracle QQ群:54775466
期待您的一起探讨
欢迎爱好者入群学习
select student_id,student_name,
sum(case when class='English' then score else 0 end) as English,
sum(case when class='Maths' then score else 0 end) as Maths,
sum(case when class='Chinese' then score else 0 end) as Chinese
from student
group by student_id,student_name;
select student_id,student_name,sum(case class when 'English' then score end) as English,sum(case class when 'Maths' then score end) as Maths,sum(case class when 'Chinese' then score end) as Chinese from student group by student_id,student_name
如果你用的SQL 2005可以这样写
select * from student pivot(sum(score) for class in([English],[Maths],[Chinese]) as t
order by student_id 就可以了吧select student_id,student_name,
decode(Class, 'English ', Score, 0) English,
decode(Class, 'Maths ', Score, 0) Maths,
decode(Class, 'Chinese ', Score, 0) Chinese
from student
group by student_id