MySQL当月负毛利订单明细_20161027
时间：2021-07-01 10:21:17
帮助过：6人阅读
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_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))
MySQL当月负毛利订单明细_20161027
标签：current code interval mysq log color pre nbsp ase
