#当月汇总 及负毛利汇总
SELECT e.ID,e.city AS 城市
,f.当月销售总额,f.当月成本总额,f.当月毛利总额,f.当月优惠券总额,f.当月赠品总额,f.当月毛利总额-f.当月优惠券总额-f.当月赠品总额 AS 当月净毛利总额
,SUM(e.销售确认额) AS 销售确认额,SUM(e.成本额) AS 订单成本额,SUM(e.优惠额) AS 优惠券金额,SUM(e.赠品额) AS 赠品额,SUM(e.毛利3) AS 净毛利
,f.当月订单总数,f.当月用券订单总数,f.当月赠品订单总数
,COUNT(e.订单号) AS 负毛利订单数,SUM(IF(e.标识2="使用优惠券负毛利",1,NULL)) AS 用券订单数,SUM(IF(e.标识3="有赠品负毛利",1,NULL)) AS 有赠品订单数
FROM (
SELECT c.ID,a.city,a.username,a.订单日期,a.订单号,a.销售确认额,a.成本额,a.毛利1,
CASE
WHEN a.毛利1<0 THEN "负毛利"
WHEN a.毛利1=0 THEN "负毛利"
WHEN a.毛利1>0 THEN "正毛利"
ELSE NULL END AS 标识1,
b.优惠额,
(a.毛利1-IFNULL(b.优惠额,0)) AS 毛利2,
CASE
WHEN a.毛利1=(a.毛利1-IFNULL(b.优惠额,0)) AND (a.毛利1-IFNULL(b.优惠额,0))<0 THEN "未使用优惠券负毛利"
WHEN a.毛利1=(a.毛利1-IFNULL(b.优惠额,0)) AND (a.毛利1-IFNULL(b.优惠额,0))=0 THEN "未使用优惠券0毛利"
WHEN a.毛利1=(a.毛利1-IFNULL(b.优惠额,0)) AND (a.毛利1-IFNULL(b.优惠额,0))>0 THEN "未使用优惠券正毛利"
WHEN a.毛利1>(a.毛利1-IFNULL(b.优惠额,0)) AND (a.毛利1-IFNULL(b.优惠额,0))<0 THEN "使用优惠券负毛利"
WHEN a.毛利1>(a.毛利1-IFNULL(b.优惠额,0)) AND (a.毛利1-IFNULL(b.优惠额,0))=0 THEN "使用优惠券负毛利"
WHEN a.毛利1>(a.毛利1-IFNULL(b.优惠额,0)) AND (a.毛利1-IFNULL(b.优惠额,0))>0 THEN "使用优惠券正毛利"
ELSE NULL END AS 标识2,
a.赠品额,
(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AS 毛利3,
CASE
WHEN (a.毛利1-IFNULL(b.优惠额,0))=(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AND (a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))<0 THEN "无赠品负毛利"
WHEN (a.毛利1-IFNULL(b.优惠额,0))=(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AND (a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))=0 THEN "无赠品0毛利"
WHEN (a.毛利1-IFNULL(b.优惠额,0))=(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AND (a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))>0 THEN "无赠品正毛利"
WHEN (a.毛利1-IFNULL(b.优惠额,0))>(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AND (a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))<0 THEN "有赠品负毛利"
WHEN (a.毛利1-IFNULL(b.优惠额,0))>(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AND (a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))=0 THEN "有赠品0毛利"
WHEN (a.毛利1-IFNULL(b.优惠额,0))>(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0)) AND (a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))>0 THEN "有赠品正毛利"
ELSE NULL END AS 标识3,
(a.毛利1-IFNULL(b.优惠额,0)-IFNULL(a.赠品额,0))/a.销售确认额 AS 净毛利率
FROM (
SELECT a1.city,a1.username,DATE(a1.订单日期) AS 订单日期,a1.订单号,a1.销售员,SUM(销售额) AS 销售确认额,SUM(毛利额) AS 毛利1,SUM(成本额) AS 成本额,SUM(IF(销售额=0 AND 成本额>0,成本额,NULL)) AS 赠品额
FROM `a005_account` AS a1
WHERE a1.订单日期>=DATE_ADD(DATE_ADD(LAST_DAY(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY)),INTERVAL 1 DAY),INTERVAL -1 MONTH) AND a1.订单日期<CURRENT_DATE
GROUP BY a1.订单号
) AS a
LEFT JOIN (#当月每天每个订单ID优惠额
SELECT b1.city,b1.username,DATE(b1.使用时间) AS 使用时间,b1.订单号,SUM(优惠券金额) AS 优惠额
FROM `a016_order_customercoupon_xref` AS b1
WHERE b1.使用时间>=DATE_ADD(DATE_ADD(LAST_DAY(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY)),INTERVAL 1 DAY),INTERVAL -1 MONTH) AND b1.使用时间<CURRENT_DATE
GROUP BY b1.订单号
) AS b ON a.订单号=b.订单号
LEFT JOIN `a000_city` AS c ON c.city=a.city
LEFT JOIN `a001_resterant` AS d ON d.username=a.username
HAVING (a.毛利1-IFNULL(a.赠品额,0)-IFNULL(b.优惠额,0))<0
ORDER BY c.ID,a.订单日期,(a.毛利1-IFNULL(a.赠品额,0)-IFNULL(b.优惠额,0))
) AS e
LEFT JOIN (
SELECT f3.city
,COUNT(f3.订单号) AS 当月订单总数,SUM(IF(f3.是否使用优惠券="使用优惠券",1,NULL)) AS 当月用券订单总数,SUM(IF(f3.是否有赠品="有赠品",1,NULL)) AS 当月赠品订单总数
,SUM(f3.当月销售额) AS 当月销售总额,SUM(f3.当月成本额) AS 当月成本总额,SUM(f3.当月毛利额) AS 当月毛利总额
,SUM(f3.优惠额) AS 当月优惠券总额
,SUM(f3.赠品额) AS 当月赠品总额
FROM (
SELECT f1.city,f1.username,f1.订单号
,SUM(f1.销售额) AS 当月销售额,SUM(f1.毛利额) AS 当月毛利额,SUM(f1.成本额) AS 当月成本额
,SUM(IF(f1.销售额=0 AND f1.成本额>0,f1.成本额,NULL)) AS 赠品额,CASE WHEN SUM(IF(f1.销售额=0 AND f1.成本额>0,f1.成本额,NULL)) IS NULL THEN "无赠品" ELSE "有赠品" END AS 是否有赠品
,f2.优惠额,CASE WHEN f2.优惠额 IS NULL THEN "未使用优惠券" ELSE "使用优惠券" END AS 是否使用优惠券
FROM `a005_account` AS f1
LEFT JOIN (
SELECT f2.city,f2.username,DATE(f2.使用时间) AS 使用时间,f2.订单号,SUM(优惠券金额) AS 优惠额
FROM `a016_order_customercoupon_xref` AS f2
WHERE f2.使用时间>=DATE_ADD(DATE_ADD(LAST_DAY(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY)),INTERVAL 1 DAY),INTERVAL -1 MONTH) AND f2.使用时间<CURRENT_DATE
GROUP BY f2.订单号
) AS f2 ON f1.订单号=f2.订单号
WHERE f1.订单日期>=DATE_ADD(DATE_ADD(LAST_DAY(DATE_ADD(CURRENT_DATE,INTERVAL - 1 DAY)),INTERVAL 1 DAY),INTERVAL -1 MONTH) AND f1.订单日期<CURRENT_DATE
GROUP BY f1.订单号
) AS f3
GROUP BY f3.city
) AS f ON e.city=f.city
GROUP BY e.ID