Oracle利用case when 或 decode——行列转换
题目一:
中国移动sql面试题:
create table test(
id number(10) primary key,
type number(10) ,
t_id number(10),
value varchar2(10)
);
字段注释:
create table test(
id number(10) primary key,--id 唯一的编号
type number(10) , -- 1 是姓名 2 性别 3 年龄
t_id number(10), --某个人 的id
value varchar2(10) -- 对应的值值
);
insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');
--
insert into test values(101,1,2,'刘二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');
insert into test values(102,1,3,'刘三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');
请写出一条查询语句结果如下:
姓名 性别 年龄
--------- -------- ----
张三 男 50
根据Type值进行查询,产生三个虚表;将这三个虚表按t_id进行等值连接,然后提取需要值:
Answer:
select t1.value as "NAME",t2.value as "SEX",t3.value as "AGE"
from (select * from test t where t.type = 1) t1
join (select * from test t where t.type = 2) t2
on t1.t_id = t2.t_id
join (select * from test t where t.type = 3) t3
on t2.t_id = t3.t_id
题目二:
2.一道SQL语句面试题,关于group by
表内容:
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下