MySQL活动期间制定月份注册用户下单情况_20161029

在10.29到10.31号期间 10月新注册的用户订单金额满600元赠与优惠券

#3天内订单满600元且10月注册的用户订单明细
SELECT a.城市,a.用户ID,b.用户名称,DATE(b.注册时间)AS 注册日期,b.用户地址,b.联系电话,a.销售员,a.订单日期,a.订单额,a.标识
FROM (#取这3天金额最高的一天
    SELECT a3.城市,a3.用户ID,a3.订单日期,a3.销售员,a3.订单额,CASE WHEN a3.订单额>=600 THEN ">=600" ELSE NULL END AS 标识
    FROM (#10月注册用户 在这3天每天订单额 按用户升序 金额降序
        SELECT a1.城市,a1.用户ID,DATE(a1.订单日期) AS 订单日期,a1.销售员,SUM(a1.金额) AS 订单额
        FROM `a003_order` AS a1
        WHERE a1.城市="长春" AND a1.订单日期>="2016-10-29" AND a1.订单日期<"2016-11-01" AND a1.金额>0 
        AND a1.用户ID IN (
            SELECT a2.用户ID FROM `a001_resterant` AS a2 WHERE a2.注册时间>="2016-10-01" AND a2.注册时间<"2016-11-01"
            )
        GROUP BY a1.用户ID,DATE(a1.订单日期)
        ORDER BY a1.用户ID,SUM(a1.金额) DESC 
    ) AS a3
    GROUP BY a3.用户ID
) AS a
LEFT JOIN `a001_resterant` AS b ON a.用户ID=b.用户ID
WHERE a.标识=">=600"
ORDER BY a.订单额 DESC