# 统计N次下单率， 能不能做成存储过程啊！！！！

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

group by 顾客ID having count(1)=2)
and 下单时间 > '20150731' AND 下单时间 < '20150802')
as s1

...全文
56 点赞 收藏 3

3 条回复

Yole 2015-11-18
group by 顾客ID having count(1)=2 把2换成参数，传几就是几次的下单的。

zbdzjx 2015-11-18

shoppo0505 2015-11-17

2.1w+

MS-SQL Server 疑难问题

2015-11-17 08:07