mysql交叉表的写法
这篇文章主要讲解了"mysql交叉表的写法"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"mysql交叉表的写法"吧！
创建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 () 就可以了
感谢各位的阅读，以上就是"mysql交叉表的写法"的内容了，经过本文的学习后，相信大家对mysql交叉表的写法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
