Mysql group with rollup应用以及游标的应用
来源:互联网 发布:北津学院教务网络 编辑:程序博客网 时间:2024/06/06 17:51
现有如下两张表:
城市表(city)
金额表(money)
需要统计每个城市各月的金额及总额和所有城市每个月的金额和总额。
首先按月份统计各城市的金额
SELECT IFNULL(A.name,'total') as name,SUM(A.pay_money) AS pay_money,A.date_time as date_time FROM( SELECT c.name,m.pay_money AS pay_money,DATE_FORMAT( from_unixtime(m.order_time),'%Y%m') as date_time FROM money m INNER JOIN city c ON c.id = m.cid)AS A GROUP BY date_time, name WITH ROLLUP HAVING date_time is NOT NULL
这里有几个函数介绍一下:
IFNULL(exp1,exp2),如果exp1为null,则返回exp2,否则返回exp1。
IF(exp1,exp2,exp3),如果exp1是TRUE(exp1<>0且exp1<>NULL),那么IF()返回exp2,否则它返回exp3。
with rollup 组内聚合。
结果如下:
然后进行嵌套查询,按照城市进行统计
SELECT IFNULL(date_time,'总计')AS '月份',SUM(if(name = '深圳',pay_money,0))as '深圳',SUM(if(name = '广州',pay_money,0))as '广州',SUM(if(name = '东莞',pay_money,0))as '东莞',SUM(if(name = '惠州',pay_money,0))as '惠州',SUM(if(name='total',pay_money,0)) as '总计'FROM ( SELECT IFNULL(A.name,'total') as name,SUM(A.pay_money) AS pay_money,A.date_time as date_time FROM( SELECT c.name,m.pay_money AS pay_money,DATE_FORMAT( from_unixtime(m.order_time),'%Y%m') as date_time FROM money m INNER JOIN city c ON c.id = m.cid)AS A GROUP BY date_time, name WITH ROLLUP HAVING date_time is NOT NULL )AS B GROUP BY date_time WITH ROLLUP
目前实现的只是静态的,动态的需要用到存储过程的cursor,也就是游标,cursor是一个可读的标识,用来标识数据取到什么地方了。 可以在存储过程,方法和触发器中创建。cursor用于一行一行的迭代的访问查询返回的结果。它不同与普通的sql 语句,一次将所有的结果返回。这样做有什么好处呢?方便你一行一行的操作你的结果啊!
存储过程代码如下:
begindeclare v_1 varchar(1000) CHARACTER SET utf8 default ' SELECT IFNULL(date_time,\'总计\')AS 月份 ' ;declare v_2 varchar(1000) CHARACTER SET utf8 default ' FROM ( SELECT IFNULL(A.name,\'total\') as name,SUM(A.pay_money) AS pay_money,A.date_time as date_time FROM( SELECT c.name,m.pay_money AS pay_money,DATE_FORMAT( from_unixtime(m.order_time),\'%Y%m\') as date_time FROM money m INNER JOIN city c ON c.id = m.cid)AS A GROUP BY date_time, name WITH ROLLUP HAVING date_time is NOT NULL)AS B GROUP BY date_time WITH ROLLUP' ;-- 定义临时参数 declare v_temp varchar(2000) CHARACTER SET utf8;-- 定义要遍历的变量declare v_city varchar(100) CHARACTER SET utf8;-- 定义结束变量declare stop int default 0;-- 定义游标 去查询城市declare cur cursor for select name from city ;-- 一个没找到的回调设置declare continue handler for not found set stop = 1; -- 游标 遍历 拼接sql字符串OPEN cur; FETCH cur INTO v_city; WHILE stop = 0 DO if v_temp = '' thenset v_temp = CONCAT(v_1,'sum(if(name =\'',v_city,'\'');set v_1 = CONCAT(v_temp,',pay_money,0)) as ',v_city);else set v_temp = '';set v_temp = CONCAT(v_1,',','sum(if(name =\'',v_city,'\'','');set v_1 = CONCAT(v_temp,',pay_money,0)) as ',v_city);end if;FETCH cur INTO v_city; END WHILE;CLOSE cur; set @v_result = CONCAT(v_1,', sum(if(name=\'total\',pay_money,0)) as \'总计\'');set @v_result = CONCAT(@v_result,v_2);-- 执行sqlprepare stmt from @v_result;EXECUTE stmt ;deallocate prepare stmt;end
下面再新增两个城市,调用存储过程一样可以取出数据。
阅读全文
0 0
- Mysql group with rollup应用以及游标的应用
- mysql group by with rollup
- mysql的group by ...... rollup
- 使用 GROUP BY 的 WITH ROLLUP
- group by with rollup
- mysql的group by应用
- 使用mysql中的with rollup得到group by的汇总信息
- MYSQL group by 语句中的with rollup是用来干什么的?
- 使用mysql中的with rollup得到group by的汇总信息
- 使用mysql中的with rollup得到group by的汇总信息
- mysql group by 使用技巧及其with rollup使用
- mysql 使用group by with rollup分组聚合信息
- Group by all | with cube | with rollup 的3用法
- Group by all | with cube | with rollup 的用法
- Mysql 游标应用
- mysql group by和having的应用
- SQL 之Group by with Rollup 的效果
- SQL 之Group by with Rollup 的效果
- 工程文件vs MAKEFile
- Andorid之打包出现Proguard returned with erro code 1.See console解决办法
- EventBus使用笔记
- memcached安装及使用
- fatal: Cannot update paths and switch to branch 'master' at the same time.
- Mysql group with rollup应用以及游标的应用
- 使用css3 transform 属性来变换背景图方法步骤详解
- Itext图片转PDF
- CAS单点登录原理
- sublime text3 插件sftp使用方法 快捷键 timeout connect 连接超时解决
- UE4(一)快速上手入门学习
- AC自动机代码模板
- ThinkPad黑将S笔记本进bios设置u盘启动教程
- (6) 什么是数据对象--每天五分钟学习数据结构