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交叉表的写法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！