#当月汇总 及负毛利汇总
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