利用mysql自定义方法,创建能计算出销量的方法
DROP FUNCTION IF EXISTS `ecshop`.`getSaleNum`$$
CREATE FUNCTION `ecshop`.`getSaleNum`(goodsid int) returns int
BEGIN
DECLARE sale_number int DEFAULT '';
SET sale_number=(SELECT sum(goods_number) FROM ecs_order_goods where goods_id=goodsid and order_id in (select order_id from ecs_order_info where order_status=1));
RETURN sale_number;
END $$
* mysql自定义方法如下:
DROP FUNCTION IF EXISTS `ecshop`.`getSaleNum`$$
CREATE FUNCTION `ecshop`.`getSaleNum`(goodsid int) returns int
BEGIN
DECLARE sale_number int DEFAULT '';
SET sale_number=(SELECT sum(goods_number) FROM ecs_order_goods where goods_id=goodsid and order_id in (select order_id from ecs_order_info where order_status=1));
RETURN sale_number;
END $$
DELIMITER;
* 使用:(SELECT getSaleNum(g.goods_id)) as sale_number
ecshop为数据库名
getSaleNum为方法名
goodsid为int类型的参数