[MySQL]两道经典SQL面试题(行转列/列转行/求最值)
解题过程在下方,由于使用的是MySQL所以没有pivot,rownum等便利,会有点小麻烦.
-- 行转列准备数据
create table t1 (
key1 VARCHAR(10),
key2 VARCHAR(10),
value int(10)
);
insert into t1 values ('a','b',1);
insert into t1 values ('a','b',2);
insert into t1 values ('a','b',3);
insert into t1 values ('c','d',1);
insert into t1 values ('c','d',2);
insert into t1 values ('c','d',3);
select * from t1;
-- 列转行准备数据
create table t2 (
key1 VARCHAR(10),
key2 VARCHAR(10),
value1 int(10),
value2 int(10),
value3 int(10)
);
insert into t2 values('a','b',1,2,3);
insert into t2 values('c','d',1,2,3);
select * from t2;
-- 行转列
select * from t1;
select key1,key2,max(case value when 1 then 1 end) as value1,
max(case value when 2 then 2 end) as value2,
max(case value when 3 then 3 end) as value3
from t1
group by key1,key2;
-- 列转行
select * from t2;
select key1,key2,value1 as value from t2
union select key1,key2,value2 as value from t2
union select key1,key2,value3 as value from t2
order by key1,key2,value
-- 求出学费最多学生id
create table table1 (
stu_id int(10),
course_id int(10)
);
create table table2 (
course_id int(10),
fee int(10)
);
insert into table1 values (1001,1);
insert into table1 values (1001,2);
insert into table1 values (1001,3);
insert into table1 values (1002,1);
insert into table1 values (1002,2);
insert into table1 values (1003,1);
insert into table1 values (1003,3);
insert into table2 values (1,160);
insert into table2 values (2,80);
insert into table2 values (3,240);
select * from table1;
select * from table2;
select stu_id from
(select stu_id,sum(fee) sum from table1
natural join table2 group by stu_id) as t
where sum=(select max(sum) from
(select stu_id,sum(fee) sum from table1
natural join table2 group by stu_id) tt);