mysql交叉表的写法

81次阅读

共计 2600 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章主要讲解了“mysql 交叉表的写法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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 () 就可以了