写入数据库是记得把/** **/全部删除,不然会保存不了
begin
/**event body**/
TRUNCATE table b_temp;
/**清空临时表**/
set @now_time = now();
/**获取当前时间**/
INSERT INTO b_temp(pid,proKuanshi,proNum) select * from
(select pid,proKuanshi,sum(proNum) from
(select * FROM `c` WHERE
`order_num` in
(SELECT `order_num` FROM `a` WHERE
`order_state` = '0'
and `pay_state` = '0'
and `order_time` < (select date_sub(@now_time, interval 30 MINUTE))
/**选择订单状态为进行中,支付状态为未付款并且订单时间距离现在超过30分钟的订单号**/
)
) as aa
/**根据上面获取到的订单号,在订单详情表里获取所有订单号下的相关商品**/
GROUP BY pid,proKuanshi
/**将所有商品根据商品id和款式进行分类,利用group by进行分组,并通过sum()进行某个商品id的某个款式的数量总和**/
) as bb ;
/**将查到的商品id,商品款式,商品数量总和存入临时表**/
update b INNER JOIN b_temp on b.id = b_temp.pid and b.kuanshi = b_temp.proKuanshi set b.kucun = b.kucun+b_temp.proNum;
/**联表查询,将库存恢复(需要注意联表查询的时候,查询时,on和条件放在最后,而如果是更新,on需要放在set之前。)**/
UPDATE a set `order_state` = '2' WHERE
`order_state` = '0'
and `pay_state` = '0'
and `order_time` < (select date_sub(@now_time, interval 30 MINUTE));
/**将所有超时未付款订单的状态改为取消订单**/
TRUNCATE table b_temp;
/**清空临时表**/
end