数据库为oracle
表是这个样子
STR COURSE SCORE
-------------------------------- -------------------------------- ----------
Joe English 99
Joe Math 98
Joe Chinese 97
Su English 96
Su Math 95
Su Chinese 94
怎么样能查询成下面这个结果集?
STR ENGLISH MATH CHINESE
-------------------------------- ---------- ---------- ----------
Joe 99 98 97
Su 96 95 94
附sql建表语句:
create table emp(
str varchar(20),
course varchar(20),
score number(11)
);insert into emp(str,course,score) values('joe','english',99);
insert into emp(str,course,score) values('joe','chinese',80);
insert into emp(str,course,score) values('joe','math',69);
insert into emp(str,course,score) values('se','english',77);
insert into emp(str,course,score) values('se','chinese',88);
insert into emp(str,course,score) values('se','math',100);
貌似要用到decode。
表是这个样子
STR COURSE SCORE
-------------------------------- -------------------------------- ----------
Joe English 99
Joe Math 98
Joe Chinese 97
Su English 96
Su Math 95
Su Chinese 94
怎么样能查询成下面这个结果集?
STR ENGLISH MATH CHINESE
-------------------------------- ---------- ---------- ----------
Joe 99 98 97
Su 96 95 94
附sql建表语句:
create table emp(
str varchar(20),
course varchar(20),
score number(11)
);insert into emp(str,course,score) values('joe','english',99);
insert into emp(str,course,score) values('joe','chinese',80);
insert into emp(str,course,score) values('joe','math',69);
insert into emp(str,course,score) values('se','english',77);
insert into emp(str,course,score) values('se','chinese',88);
insert into emp(str,course,score) values('se','math',100);
貌似要用到decode。
seq --序列
jcxm --检查项目
zhi --值 数据分别如下:
seq jcxm zhi
------- -------- --------
11 1 0.50
11 2 0.21
11 3 0.25
12 1 0.24
12 2 0.30
12 3 0.22 实现功能
创建视图时移动行值为列值
create view v_view1
as
select seq,
sum(decode(jcxm,1, zhi)) 检测项目1,
sum(decode(jcxm,2, zhi)) 检测项目2,
sum(decode(jcxm,3, zhi)) 检测项目3
from ttt
group by seq; 序号 检测项目1 检测项目2 检测项目3
11 0.50 0.21 0.25
12 0.24 0.30 0.22
select str ,
max(case course when 'english' then score else 0 end) chinese,
max(case course when 'chinese' then score else 0 end) math,
max(case course when 'math' then score else 0 end) english
from emp
group by str;
SUM (DECODE (course, 'chinese', score, 0)) chinese,
SUM (DECODE (course, 'math', score, 0)) math
FROM emp
GROUP BY str