简单的存储过程

 

利用navicat 简单生成的,数据表.每天的销售数据.

可以把日期作为参数,输入.得出数据.也行

DROP PROCEDURE IF EXISTS `daily_sales`;

CREATE DEFINER = `hbx`@`%` PROCEDURE `daily_sales`()
BEGIN
SELECT
t.org_name,
SUM(td.pay_amount) AS all_money,
COUNT(DISTINCT t.consignee_phone) AS trade_man ,
SUM(td.pay_amount)/ COUNT(DISTINCT t.consignee_phone) AS menmber_average
FROM trade_detail_onl AS td
LEFT JOIN trade_onl as t ON t.id = td.trade_onl_id
WHERE
t.confirm_time BETWEEN  DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 day), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
AND t.status  BETWEEN 2 AND 8
AND t.org_id_erp NOT IN ( 4000 , 25194)
GROUP BY t.org_id_erp
ORDER BY SUM(td.pay_amount)DESC ;
END;