MySQL当月汇总 及负毛利汇总_20161027

#当月汇总 及负毛利汇总

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