统计N次下单率, 能不能做成存储过程啊!!!!
萧寒往往 2015-11-17 08:07:42 select
SUM(总价)as 二次客单总金额,count(*) as 二次客单量 ,(SUM(总价)/count(*)) as 二次客单平均价,
(select count(*) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' ) as 总单量 ,
(select SUM(总价) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' ) as 总金额 ,
(count(*)* 1.000/(select count(*) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' ))
as 二次客单单量占比 ,
(SUM(总价)* 1.000/(select SUM(总价) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' ))
as 二次客单金额占比 ,
(select SUM(优惠券面值) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' )
as 优惠券总金额 ,
(select SUM(总价-优惠券面值) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' and 付款方式='余额支付')
as 余额支付 ,
(select SUM(总价-优惠券面值) from she$ where 下单时间 > '20150731' AND 下单时间 < '20150802' and 付款方式='在线支付')
as 在线支付
from
(select * from she$
where 顾客ID in (select 顾客ID from (select * from she$ where 下单时间 > '20150701' AND
下单时间 < '20150802')as ass
group by 顾客ID having count(1)=2)
and 下单时间 > '20150731' AND 下单时间 < '20150802')
as s1
上面是查询 ,怎么做成一个存储过程 调用的时候改下日期就可以了 上面有三种日期,最下面的开始日期是不变的 ,上面的日期都是对应的 一天。 还这是统计二次下单的 ,能不能把三次四次五次 等等的数据汇总成一个表。。