SELECT e.城市,e.用户ID,i.联系电话,h.订单日期,h.订单ID,h.销售员,h.订单额,e.优惠券ID,e.优惠券名称,CASE WHEN h.优惠额 IS NULL THEN "未使用优惠券" ELSE "使用优惠券" END AS 发放的餐馆是否使用优惠券,e.优惠券档位,h.优惠额
FROM (
    SELECT a1.城市,a1.用户ID,DATE(a1.发放日期) AS 发放日期,a1.优惠券ID,a1.优惠券名称,RIGHT(a1.优惠券名称,CHAR_LENGTH(a1.优惠券名称)-CHAR_LENGTH(LEFT(a1.优惠券名称,INSTR(a1.优惠券名称,"【")-1))) AS 优惠券档位
    FROM `a015_customer_coupon` AS a1
    WHERE a1.城市="杭州" AND a1.发放日期>="2016-10-23" AND a1.发放日期<"2016-10-26" AND a1.优惠券ID>=939 AND a1.优惠券ID<=951 
    GROUP BY a1.用户ID
) AS e
LEFT JOIN (
    SELECT f.*,g.优惠券ID,g.优惠券名称,g.优惠额
    FROM (
        SELECT f1.城市,f1.用户ID,DATE(订单日期) AS 订单日期,f1.订单ID,f1.销售员,SUM(金额) AS 订单额
        FROM `a003_order` AS f1 
        WHERE f1.金额>0 AND  f1.订单日期 >="2016-10-25" AND f1.订单日期<"2016-11-20" 
        GROUP BY f1.订单ID
        ) AS f 
        LEFT JOIN (
            SELECT b1.城市,DATE(b1.使用时间) AS 使用日期,b1.用户ID,b1.订单号,b1.优惠券ID,b1.优惠券名称,SUM(b1.优惠券金额) AS 优惠额
            FROM `a016_order_customercoupon_xref`AS b1
            WHERE b1.城市="杭州" AND b1.使用时间>="2016-10-25" AND b1.使用时间<"2016-11-20" AND b1.优惠券ID>=939 AND b1.优惠券ID<=951 
            GROUP BY b1.订单号
        ) AS g ON g.订单号=f.订单ID
) AS h ON e.用户ID=h.用户ID
LEFT JOIN `a001_resterant` AS i ON i.用户ID=e.用户ID
WHERE e.优惠券档位<>"无优惠券" 
ORDER BY FIELD(发放的餐馆是否使用优惠券,"使用优惠券","未使用优惠券")