day3

中国移动sql面试题：

create table test(
id number(10) primary key,
type number(10) ,
t_id number(10),
value varchar2(6)
);

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');

*/

/*
利用一条sql语句将上表的数据显示为如下内容：
姓名 性别 年龄
----------------------
张三 男 50
刘二 男 30
刘三 女 10
*/

--使用分组方式
select max(decode(type, 1, value)) "姓名",
max(decode(type, 2, value)) "性别",
max(decode(type, 3, value)) "年龄"
from test
group by t_id
--me
select min(decode(t.type, 1, value)),
min(decode(t.type, 2, value)),
min(decode(t.type, 3, value))
from test t
group by t.T_ID;

--使用表连接方式
select t1.value "姓名",t2.value "性别",t3.value "年龄" from
(select value,t_id from test where type = 1) t1 ,
(select value,t_id from test where type = 2) t2 ,
(select value,t_id from test where type = 3) t3 where t1.t_id = t2.t_id and t1.t_id = t3.t_id