mysql交叉表的寫法

這篇文章主要講解了“mysql交叉表的寫法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“mysql交叉表的寫法”吧！

 
創(chuàng)建2張表 一張t_shuiguo 水果表 一張t_supermarket 超市表
 
現(xiàn)在我要查一個(gè)超市的各區(qū)水果價(jià)格的匯總
 
如下: 表A
 

那么首先水果表 是可以動態(tài)添加的 所有A表中的列 是動態(tài)的 先不考慮
 
先看下靜態(tài)的 如果就是這么4個(gè)水果
 
那么SQL可以這么寫 (參考了網(wǎng)上一些列子)
 
-- 靜態(tài)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
 

 
然后比較費(fèi)勁的就是動態(tài)的 需要用到存儲過程
 
如下：
 
001
-- 定義存儲過程結(jié)束符
002
 
003
delimiter $$
004
 
005
-- 有先刪除 再創(chuàng)建過程
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
-- 定義臨時(shí)參數(shù) 
040
 
041
declare v_temp varchar(2000);
042
 
043
-- 定義要遍歷的變量
044  www.2cto.com  
 
045
declare v_shuiguo varchar(100) ;
046
 
047
-- 定義結(jié)束變量
048
 
049
declare stop int default 0;
050
 
051
-- 定義游標(biāo) 去查水果列表
052
 
053
declare cur cursor for select code from t_shuiguo ;
054
 
055
-- 一個(gè)沒找到的回調(diào)設(shè)置
056
 
057
declare continue handler for not found set stop = 1; 
058
 
059
-- 游標(biāo) 遍歷 拼接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
-- 執(zhí)行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交叉表的寫法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對mysql交叉表的寫法這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！