SQL经典模式 - 行转列

不知道读者有没有类似的遭遇，遇到下面这样的设计：

#雇员信息
create table employee (eId int, propName varchar(10), propName varchar(10));

insert into employee values (1, 'firstName','Calvin'),(1,'lastName','Zhou'),(1,'age','25'), (1,'gender','1')
insert into employee values (2, 'firstName','Grace'),(2,'lastName','Lai'),(2,'age','28'), (2,'gender','0')
insert into employee values (3, 'firstName','Morgan'),(3,'age','28');


#雇员考评
create table score (eId int, score int);

insert into score (1,85), (2,60), (3,90), (4,20);


#用一条SQL语句找出考评在 80分以上， 年龄在28岁以下，并且性别是男 的雇员所有信息

遇到这个需求的时候，开发人员一般都要在心里开始访问DBA的家人了，他妈怎么设计的表，干嘛不把employee的属性集中放一行，搞得都没法查

于是，一般的做法：
1.先找一遍score表，查出所有的 score>85的eId，
2.然后再去employee表把这些Id相关的信息都加到内存里面，比较属性名，拼成一个Employee对象，
3.最后再循环这个对象，对age，和gender属性进行过滤


这个过程麻烦的要死，但如果熟练掌握行转列的技巧，那么就是轻而易举一条语句就查出了所有复合要求的Employee对象


这个技巧就是 case when, 配合group by，以及聚合函数 max或者min 使用：

步骤一， case when， 将行拆到列上面来， 没有值的列用Null表示

select
eId,
case propName when 'firstName' then propValue else null end as firstName,
case propName when 'lastName' then propValue else null end as lastName,
case propName when 'age' then propValue else null end as age,
case propName when 'gender' then propValue else null end as gender
from employee;

步骤二：然后对表进行group by，每个eId一个组：

select
eId,
case propName when 'firstName' then propValue else null end as firstName,
case propName when 'lastName' then propValue else null end as lastName,
case propName when 'age' then propValue else null end as age,
case propName when 'gender' then propValue else null end as gender
from employee group by eId;

步骤三： 用max，过滤掉所有的null

select
eId,
max(case propName when 'firstName' then propValue else null end) as firstName,
max(case propName when 'lastName' then propValue else null end) as lastName,
max(case propName when 'age' then propValue else null end) as age,
max(case propName when 'gender' then propValue else null end) as gender
from employee group by eId;


有了上面这个employee的临时表，就好办了，在这个基础上，添加Score条件的过滤：


select
eId,
max(case propName when 'firstName' then propValue else null end) as firstName,
max(case propName when 'lastName' then propValue else null end) as lastName,
max(case propName when 'age' then propValue else null end) as age,
max(case propName when 'gender' then propValue else null end) as gender
from employee
where eId in (select eId from score where score>80)
group by eId;


最后再添加上employee本身条件28岁以下，男

select tmp.* from (..) as tmp where tmp.age<28 and tmp.gender=1