SELECT c.ID,a.city AS 城市,a.username AS 用户ID,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_rest` 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))