SQL经典模式--列转行[转载]
一般需要将列转成行来使用，一定是原有的Schema设计没有考虑周全。但是没有办法，为了保护现有的投资，不得不在糟糕的设计上周旋，用最小的代价去实现新需求。毕竟认识都是由浅入深，为不健全的Schema设计付出代价，就像交税一样，无可避免。
举例：
课程表： 每门课程由5位老师教，要求包含老师的信息，以及一些课程的信息
create table cource (id int, name varchar(100), teacher1 int,teacher2 int,teacher3 int, teacher4 int, teacher5 int);
insert into cource values (1,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (2,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (3,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (4,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (5,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (6,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (7,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (8,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (9,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (10,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (11,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (12,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (1,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (2,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (3,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (4,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (5,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (6,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (7,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (8,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (9,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (10,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (11,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (12,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
老师表： 记录了每个老师的年龄，级别，性别
create table teacher(id int, age int, level int, gender int);
insert into teacher values (1, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (2, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (3, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (4, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (5, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (6, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (7, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (8, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (9, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (10, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (11, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (12, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (13, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (14, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (1, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (2, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (3, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (4, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (5, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (6, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (7, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (8, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (9, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (10, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (11, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (12, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (13, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (14, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
需求：
找出一些课程， 这些课程是由2位以上 男老师教，并且他们的级别大于3，并且他们年龄在40以下的。
找出一些课程， 这些课程是由2位以上 男老师教，并且他们的级别大于3，并且他们年龄在40以下的。
一般过程性的方法:
先找出teacher表里面所有的teacherId (男老师教，并且他们的级别大于3，并且他们年龄在40)，得到一个set
然后，把cource表加载到内存对象里面，然后开始循环，并用计数器去统计每个teacherId属性，看是否存在于set里面，如果存在就计数器+1, 计数器>3就跳出这条记录。
毫无疑问，以上的步骤还是比较的麻烦，估计一堆代码才理的清调理。
于是列转行的模式，就应运而生了。之所以称之为模式，是因为这样的问题场景实在是太常见了，就像在java里面要解决整个系统只用一个对象的问题而总结出了单例模式一样。
列转行需要一个工具表pivot，里面只有一列，存了1，2，3... ， 你有多少个列需要转成行，就要多少个数。 我们这个例子是5
create table pivot (id int);
insert into pivot values (1),(2),(3),(4),(5);
insert into pivot values (1),(2),(3),(4),(5);
步骤一： 放大结果集，一条记录复制5条， 然后对与每条记录，根据pivot.id只取一个teacherId值，得到一个临时表
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p;
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p;
步骤二： 在临时表的基础上，再进行过滤(男老师教，并且他们的级别大于2，并且他们年龄在40)，得到合适的结果集
select tmp.name from (
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
步骤三： 分组统计，课程是由2位以上符合要求老师教的
select tmp.name from (
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
group by tmp.name having count(*)>2
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
group by tmp.name having count(*)>2
