Mysql procedure的应用
时间：2010-09-17 来源：Junw_china
今天， 接到一个需求，要求如下：
统计一个月以内，每个支付金额在每天中的数量。
比如说： 有一天， 有10000个交易，其中有1000个充值的金额为100， 有5000个是500，等等，
就是要统计每天中每个交易额的数量。
然后就想直接用mysql输出到xls文件。
下面是代码：
1 DELIMITER //
2 DROP PROCEDURE IF EXISTS test.GetAllProducts//
3 CREATE PROCEDURE test.GetAllProducts(IN start_time varchar(20),IN end_time varchar(20))
4 BEGIN
5 DECLARE i,num int default 0;
6 SET @condition = '';
7 SELECT count(distinct(amount)) into num from trade_log;
8 WHILE i < num DO
9 -- select i;
10 set @index = i;
11 set @tmp = null;
12 PREPARE stnm FROM 'SELECT distinct(amount) into @tmp from trade_log limit ?,1';
13 execute stnm using @index;
14 -- select @tmp;
15 set @condition = concat(@condition,"sum(if(amount = ",@tmp,",1,0)) as m",@tmp,",");
16 -- select @condition;
17 set i = i + 1;
18 END WHILE;
19 -- SET condition = SUBSTRING(@condition,1,LENGTH(@condition)-1);
20 SET @start = start_time;
21 SET @end = end_time;
22 SET @where = concat('create_time >= UNIX_TIMESTAMP("',@start,'") and create_time <= UNIX_TIMESTAMP("',@end,'") group by time');
23 SET @s = concat('select ',@condition,"FROM_UNIXTIME(create_time,'%Y-%m-%d') as time from trade_log where ",@where);
24 PREPARE stmt FROM @s;
25 EXECUTE stmt;
26 END //
27 DELIMITER ;
运行：
统计2010-8-17到2010-9-17的数据，
set @start = "2010-8-17";
set @end = "2010-9-17";
call GetAllProducts(@start,@end);
测试结构如下：
+------+------+------+------+------+------+------+------+------+------+------------+
| m50 | m200 | m100 | m10 | m400 | m240 | m55 | m110 | m500 | m480 | time |
+------+------+------+------+------+------+------+------+------+------+------------+
| 0 | 0 | 4 | 2 | 0 | 9 | 2 | 5 | 0 | 9 | 2010-08-17 |
| 0 | 0 | 0 | 0 | 1 | 2 | 2 | 5 | 0 | 4 | 2010-08-18 |
PS:第一次写， 有很多地方写的很傻，希望高手指点！
相关阅读 更多 +
