SQL执行顺序

from --> join on --> where --> group by --> count()sum() --> having --> order by --> limit --> distinct --> select

行转列

group by + sum(if)

华泰z卷1

use test_sql;  
set hive.exec.mode.local.auto=true;  
create table table2(year int,month int ,amount double) ;  
insert overwrite table table2 values  
    (1991,1,1.1),
    (1991,2,1.2),  
    (1991,3,1.3),  
    (1991,4,1.4),  
    (1992,1,2.1),  
    (1992,2,2.2),  
    (1992,3,2.3),  
    (1992,4,2.4);  
select * from table2;

查询每年每月的数量

case 
    when month = 1 then amount
    else 0
end

• 当case when只有两个分支，且为互斥关系，等价于if()

select year,  
    sum(if(month=1,amount,0)) m1,  
    sum(if(month=2,amount,0)) m2,  
    sum(if(month=3,amount,0)) m3,  
    sum(if(month=4,amount,0)) m4  
from table2  
group by year;

华泰z卷2

查询课程编号“2”的成绩比课程编号“1”低的所有同学的学号、姓名

create table student(sid int, sname string, gender string, class_id int);  
insert overwrite table student  
values (1, '张三', '女', 1),  
    (2, '李四', '女', 1),  
    (3, '王五', '男', 2);  
select * from student;  
create table course (cid int, cname string, teacher_id int);  
insert overwrite table course
values (1, '生物', 1),  
    (2, '体育', 1),  
    (3, '物理', 2);  
select * from course;  
create table score (sid int, student_id int, course_id int, number int);  
insert overwrite table score  
values (1, 1, 1, 58),  
    (4, 1, 2, 50),  
    (2, 1, 2, 68),  
    (3, 2, 2, 89);  
select * from score;

with t1 as(  
    select student_id,  
        sum(if(course_id=2,number,0)) as pe, --体育  
        sum(if(course_id=1,number,0)) as bio --生物  
    from score  
    group by student_id  
    having pe<bio)  
    
select sid, sname  
from t1  
join student  
on t1.student_id = sid;

腾讯游戏

查询每天胜负的人数

--建表  
create table table1(DDate string, shengfu string) ;  
insert overwrite table table1 values ('2015-05-09', "胜"),  
    ('2015-05-09', "胜"),  
    ('2015-05-09', "负"),  
    ('2015-05-09', "负"),  
    ('2015-05-10', "胜"),  
    ('2015-05-10', "负"),  
    ('2015-05-10', "负");  

• 代码-sum

select DDate,  
    SUM(case when shengfu = '胜' then 1 else 0 end) `胜`,  
    SUM(case when shengfu = '负' then 1 else 0 end) `负`  
from table1  
group by DDate;

• 代码-count

select DDate,  
    count(case when shengfu = '胜' then 1 else null end) `胜`,  
    count(case when shengfu = '负' then 1 else null end) `负`  
from table1  
group by DDate;

腾讯QQ

• table A

create table tableA(qq string, game string)  
insert overwrite table tableA values  
    (10000, 'a'),  
    (10000, 'b'),  
    (10000, 'c'),  
    (20000, 'c'),  
    (20000, 'd');

• table B

create table tableB(qq string, game string) ;  
insert overwrite table tableB values  
    (10000, 'a_b_c'),  
    (20000, 'c_d');

问题一：将tableA转成tableB的形式

select qq,  
    concat_ws('_', collect_list(game)) game  
from tableA  
group by qq;

问题二：将tableB转成tableA的形式

• 借助split 和侧视图lateral view

select qq,  
    v1.game2  
from tableB lateral view explode(split(game, '_')) v1 as game2;