MySQL业务-发放的优惠券 用户使用情况_20161028
时间：2021-07-01 10:21:17
帮助过：9人阅读
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(发放的餐馆是否使用优惠券,"使用优惠券","未使用优惠券")
MySQL业务-发放的优惠券 用户使用情况_20161028
标签：针对 res 优惠券 group 用户id class ase strong 监控
