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

创建2张表 一张t_shuiguo 水果表 一张t_supermarket 超市表现在我要查一个超市的各区水果价格的汇总如下: 表A 那么首先水果表 是可以动态添加的 所有A表中的列 是动态的 先不考虑先看下静态的 如果就是这么4个水果那么SQL可以这么写 (参考了网上一些列子)--

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

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

如下: 表A

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

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

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

-- 静态sql

01

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

02

03

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

04  www.2cto.com

05

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

06

07

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

08

09

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

10

11

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

12

13

from

14

15

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

16

17

from

18

19

(select

20

21

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

22

23

from t_supermarket  m

24

25

inner join t_shuiguo s

26

27

on m.shuiguo = s.id

28  www.2cto.com

29

) A

30

31

group by groups, name

32

33

with rollup

34

35

having groups is not null

36

37

) B

38

39

group by groups

40

41

with rollup

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

如下：

001

-- 定义存储过程结束符

002

003

delimiter $$

004

005

-- 有先删除 再创建过程

006

007

drop procedure if exists  searchShuiguo $$

008

009

create procedure searchShuiguo()

010

011

begin

012

013

-- 定义sql前端

014

015

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

016

017

-- 定义sql 后端

018

019

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

020

021

from (

022

023

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

024

025

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

026

027

) A

028

029

group by groups,

030

031

code with rollup having groups is not null

032

033

) B

034

035

group by groups

036

037

with rollup';

038

039

-- 定义临时参数

040

041

declare v_temp varchar(2000);

042

043

-- 定义要遍历的变量

044  www.2cto.com

045

declare v_shuiguo varchar(100) ;

046

047

-- 定义结束变量

048

049

declare stop int default 0;

050

051

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

052

053

declare cur cursor for select code from t_shuiguo ;

054

055

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

056

057

declare continue handler for not found set stop = 1;

058

059

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

060

061

OPEN cur;

062

063

FETCH cur INTO v_shuiguo;

064  www.2cto.com

065

WHILE stop = 0

066

067

DO

068

069

if v_temp = '' then

070

071

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

072

073

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

074

075

else

076

077

set v_temp  = '';

078

079

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

080

081

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

082  www.2cto.com

083

end if;

084

085

FETCH cur INTO v_shuiguo;

086

087

END WHILE;

088

089

CLOSE cur;

090

091

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

092

093

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

094

095

-- 执行sql

096

097

prepare stmt from @v_result;

098

099

EXECUTE stmt ;

100

www.2cto.com

101

deallocate prepare stmt;

102

103

end $$

哦了

call searchShuiguo  () 就可以了

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

作者 夜聆风