一个简单的存储过程范例

 1 delimiter $$
 2 CREATE PROCEDURE `proc_Ranking`(IN sku VARCHAR(20),IN skuname VARCHAR(20),IN status VARCHAR(20),IN sales VARCHAR(20),IN today VARCHAR(20),IN old_time VARCHAR(20))
 3 BEGIN
 4     SET @sku = sku;
 5     SET @skuname = skuname;
 6     SET @status = status;
 7     SET @sales = sales;
 8     SET @today = today;
 9     SET @old_time = old_time;
10     SET @sql_header = "SELECT
11              c.`sku`,c.`amt`,c.`avgs` avg,s.`skuname`,s.`skupic`,s.`edittime`,st.`statusname`
12         FROM(
13             SELECT
14                  `sku` , ROUND(SUM( `amt` ) , 2) 'amt' , ROUND(SUM( `avg` ) /7 , 2) 'avgs'
15             FROM `skusales`
16             WHERE    
17                  `sku` <> ''
18             AND `paytime` > @old_time
19             AND `paytime` < @today
20             GROUP BY `sku`) c
21             LEFT JOIN
22                 `skustatus` s ON c.`sku` = s.`sku`
23             LEFT JOIN
24                 `statustype` st ON s.`sale_status` = st.`pid`
25         WHERE
26             1";
27     IF @sku <> '' THEN
28     SET @sql_header = CONCAT(@sql_header," AND c.`sku` = @sku");
29     END IF;
30 
31     IF @status <> '' THEN
32     SET @sql_header = CONCAT(@sql_header," AND s.`sale_status` = @status");
33     END IF;
34 
35     IF @skuname <> '' THEN
36     SET @sql_header = CONCAT(@sql_header," AND s.`skuname` like @skuname");
37     END IF;
38 
39     IF @sales <> '' THEN
40     SET @sql_header = CONCAT(@sql_header," AND c.avgs");
41     SET @sql_header = CONCAT(@sql_header,@sales);
42     END IF;
43     SET @sql_header = CONCAT(@sql_header," ORDER BY c.amt DESC");
44 
45     SET @sql_bodys = @sql_header;
46     
47     PREPARE stmt FROM @sql_bodys;
48     EXECUTE stmt;
49     DEALLOCATE PREPARE stmt ;
50         
51     END$$

 

转载于:https://www.cnblogs.com/qq2317894314/p/5613788.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值