[MySQL]两道经典SQL面试题(行转列/列转行/求最值)

不念不忘少年蓝@ 2022-03-01 05:28 82阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NTUxOTk0NDM_size_16_color_FFFFFF_t_70]  
解题过程在下方,由于使用的是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);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NTUxOTk0NDM_size_16_color_FFFFFF_t_70]: /images/20220301/5b3f87c6b9e140de99a3288b70c392a5.png