行列转换

1.变换结果集成一行

create table test3
	(
    deptno   int           not null,
	cnt      int           not null
    );
insert into test3 values (10,3);
insert into test3 values (20,5);
insert into test3 values (30,6);
select * from test3;

原数据长这样

我们希望数据这样

一开始这样写的

select 
	sum(case when deptno = 10 then cnt else 0 end) as depno_10
    ,sum(case when deptno = 20 then cnt else 0 end) as depno_20
    ,sum(case when deptno = 30 then cnt else 0 end) as depno_30
from 
	test3
group by deptno;

结果当然不对了，你想想对于deptno=10，depno=10，dept_20=0...

正确做法

select 
	sum(case when deptno = 10 then cnt else 0 end) as depno_10
    ,sum(case when deptno = 20 then cnt else 0 end) as depno_20
    ,sum(case when deptno = 30 then cnt else 0 end) as depno_30
from 
	test3;

第一行=10，所以10=3，20=0，30=0；如上所示，后来sum就行啦

为什么group by就不可以，因为group by之后再sum就是对groupby字段中不同值进行sum，所以第一行只有sum它本身 

groupby的效果：保证对该字段的每个不同的值都当作一行，去执行select里面的操作

所以在这里10，20，30都是不同的值（groupby不仅没得用，而且强制每一行都要执行select里面的操作，有三个操作，所以会有上面=0的情况）但是不groupby直接sum就意味着对10，20，30所有的记录当作一条去执行select里面的操作就行

为了更好说明效果

我再插入两行

insert into test3 values (30,7);
insert into test3 values (30,8);

select 
	sum(case when deptno = 10 then cnt else 0 end) as depno_10
    ,sum(case when deptno = 20 then cnt else 0 end) as depno_20
    ,sum(case when deptno = 30 then cnt else 0 end) as depno_30
from 
	test3
group by deptno;

是这样，对于第一2行都是自己成一个group的，sum相当于不sum

第3，4，5是一起的，不sum的时候（006；007；008），之后sum，得到（0021）

select 
	sum(case when deptno = 10 then cnt else 0 end) as depno_10
    ,sum(case when deptno = 20 then cnt else 0 end) as depno_20
    ,sum(case when deptno = 30 then cnt else 0 end) as depno_30
from 
	test3;

得到正确的结果

3，5，21

xi

2.变换结果集成多行

create table test2
	(
    empno   int           not null,
	job     varchar(15)   not null,
    ename   varchar(15)   not null
    );
insert into test2 values (1,'A','linda');
insert into test2 values (2,'A','alice');
insert into test2 values (3,'A','juice');
insert into test2 values (4,'B','kris');
insert into test2 values (5,'B','gem');

原数据长这样

最后希望得到这样的结果

一开始本?是这样做的

select 
	case when job = 'A' then ename else null end as 'jobA'
    ,case when job = 'B' then ename else null end as 'jobB'
from
	test2;

结果是这个样子，当然不对了！你对每一行都问下case=A吗，case=B吗，所以第一条记录job A有，jobB没有

正确做法

select 
     max(case when job = 'A' then ename else null end) as 'jobA'
    ,max(case when job = 'B' then ename else null end) as 'jobB'
from
	(
    select
         a.job
        ,a.ename
        ,(select count(*) from test2 as b
         where b.job = a.job and a.empno < b.empno) as rnk
	from 
		test2 as a
	) as x
group by rnk; 

注意a.job=b.job and a.empno<b.empno;是分组（分job）排序哦！！！！

哇 这样保证rnk相同的时候都是不同类型的job，这个时候casewhen就很棒，max只是为了从结果集中剔除null 

再次感受groupby：这里对每个不同的rnk保证一行，去执行select。所以最后结果只有rnk=0的情况rnk=1/rnk=2的情况，也就是说只有三行！！！！！！！！。对于rnk=0（Job=a，job=b有2种情况，但没关系，我们还是当作一行）执行select的第一个=a那么就是ename，=b就不执行了；同理第二个条件；这样保证有2列了！

当然如果可以使用窗口函数更好

 

select 
     max(case when job = 'A' then ename else null end) as 'jobA'
    ,max(case when job = 'B' then ename else null end) as 'jobB'
from
	(
    select
         a.job
        ,a.ename
        ,row_number()over(partition by job order by ename) as rnk
	from 
		test2 
	) as x
group by rnk; 

3.反向变换结果

原来

现在 

这个时候要用笛卡尔积 

注意笛卡尔积必须是一列值才可以（行数成倍增加），一行值形成不了

比如下面这样子可以形成笛卡尔积

select 
	*
from 
	(
	select 
		sum(case when deptno = 10 then cnt else 0 end) as depno_10
		,sum(case when deptno = 20 then cnt else 0 end) as depno_20
		,sum(case when deptno = 30 then cnt else 0 end) as depno_30
	from 
	test3
    ) as a
	,(select deptno from test3) as c
;

select 
	c.deptno
    ,(case when c.deptno = 10 then depno_10
          when c.deptno = 20 then depno_20
          else depno_30
          end) as cnt
from 
	(
	select 
		sum(case when deptno = 10 then cnt else 0 end) as depno_10
		,sum(case when deptno = 20 then cnt else 0 end) as depno_20
		,sum(case when deptno = 30 then cnt else 0 end) as depno_30
	from 
	test3
    ) as a
	,(select deptno from test3) as c
;

这样写就对啦～～ 

上面这种形式的迪卡尔积是有真实意义的，

但有时候你就是想为你原始的数据增加每行都直接扩大X倍，这个时候你只需要将笛卡尔积取的另外一个列保证有四行就可以（数值什么的不重要）详细见sql经典实例第12章340页