MySQL_杭州北仓 12.3-12.7需求活动期间累计下单达到3天及以上的客户_20161212
#C025_02杭州北仓 12.3-12.7需求活动期间累计下单达到3天及以上的客户明细
SELECT d.*,CASE WHEN 下单天次>=3 THEN "下单超过3天" ELSE "下单不足3天" END AS 标识 FROM ( SELECT a.城市,仓库,a.用户ID,a.用户名称,b.用户地址,b.联系电话,c.销售员,COUNT(订单日期) AS 下单天次,SUM(订单数) AS 订单数,SUM(金额) AS 金额 FROM ( SELECT 城市,仓库,用户ID,用户名称,订单日期,COUNT(订单ID) AS 订单数,SUM(金额) AS 金额 FROM ( SELECT a1.城市,a1.用户ID,a1.用户名称,a1.订单ID,DATE(a1.订单日期) AS 订单日期,SUM(a1.金额) AS 金额,CASE WHEN a2.订单号 IS NULL THEN NULL ELSE a2.仓库 END AS 仓库 FROM a003_order AS a1 LEFT JOIN `a018_cgwy_order_car` AS a2 ON a1.订单ID=a2.订单号 WHERE a1.城市="杭州" AND a1.订单日期>='2016-12-03' AND a1.订单日期<'2016-12-08' AND a1.订单日期<CURRENT_DATE AND a1.金额>0 AND a2.仓库='杭州北仓' GROUP BY a1.订单ID ) AS a3 GROUP BY 用户ID,订单日期 ) AS a LEFT JOIN `a001_resterant` AS b ON a.用户ID=b.用户ID LEFT JOIN `a003b_order_item` AS c ON a.用户ID=c.用户ID GROUP BY a.用户ID ) AS d ORDER BY FIELD(标识,"下单超过3天","下单不足3天"),金额 DESC