mysql行列转换（交叉表）的写法

创建2张表 一张t_shuiguo 水果表 一张t_supermarket 超市表

现在我要查一个超市的各区水果价格的汇总

如下: 表A

那么首先水果表 是可以动态添加的 所有A表中的列 是动态的 先不考虑

先看下静态的 如果就是这么4个水果

那么SQL可以这么写 (参考了网上一些列子)

-- 静态sql

select ifnull(groups,'total') as groups,

sum(if(name='苹果',prices,0)) as '苹果',

    sum(if(name='梨',prices,0)) as '梨',

sum(if(name='橘子',prices,0)) as '橘子',

sum(if(name='樱桃',prices,0)) as '樱桃',

sum(if(name='total',prices,0)) as 'totals'

from

(select A.groups as groups,IFNULL(A.name,'total') as name ,sum(A.price) as prices

from

(select

m.groups as groups ,s.name as name,m.price as price

from t_supermarket  m

inner join t_shuiguo s

on m.shuiguo = s.id

) A

group by groups, name

with rollup

having groups is not null

) B

 group by groups

 with rollup

然后比较费劲的就是动态的 需要用到存储过程

如下：

-- 定义存储过程结束符

delimiter $$

-- 有先删除 再创建过程

drop procedure if exists  searchShuiguo $$

create procedure searchShuiguo()

begin

-- 定义sql前端

declare v_1 varchar(1000) default ' SELECTIFNULL(groups,\'total\') as groups ';

-- 定义sql 后端

declare v_2 varchar(1000) default ' from (select groups,IFNULL(code,\'total\') as code ,sum(A.price) as prices

from (

selectm.groups as groups ,s.code as code,m.price as price

from t_supermarket  m inner join t_shuiguo s on m.shuiguo = s.id

) A

group by groups,

code with rollup having groups is not null

) B

group by groups

with rollup';

-- 定义临时参数  

declare v_temp varchar(2000);

-- 定义要遍历的变量

declare v_shuiguo varchar(100) ;

-- 定义结束变量

declare stop int default 0;

-- 定义游标 去查水果列表

declare cur cursor for select code from t_shuiguo ;

-- 一个没找到的回调设置

declare continue handler for not found set stop = 1;  

-- 游标 遍历 拼接sql字符串

OPEN cur;

 FETCH cur INTO v_shuiguo;

 WHILE stop = 0  

DO

   if v_temp = '' then

set v_temp = CONCAT(v_1,'sum(if(code =\'',v_shuiguo,'\'');

set v_1 = CONCAT(v_temp,',prices,0)) as ',v_shuiguo);

else

   set v_temp  = '';

set v_temp = CONCAT(v_1,',','sum(if(code =\'',v_shuiguo,'\'','');

set v_1 = CONCAT(v_temp,',prices,0)) as ',v_shuiguo);

end if;

FETCH cur INTO v_shuiguo;

 END WHILE;

CLOSE cur;  

set @v_result = CONCAT(v_1,', sum(if(code=\'total\',prices,0)) as \'total\'');

set @v_result = CONCAT(@v_result,v_2);

-- 执行sql

prepare stmt from @v_result;

EXECUTE stmt ;

deallocate prepare stmt;

end $$

哦了

call searchShuiguo  () 就可以了

接下来添加一个新水果哈密瓜 试试

转载于:https://my.oschina.net/xiaofanTSZ/blog/64835