SQLsummary/活动/202109业绩冲刺活动赠品奖励2.sql

SELECT
  A.`会员编号`,
	A.`报单中心`,
	A.`订单号`,
	'已支付' 订单状态,
	'PB083' 存货编码,
	'渊源净自然均衡洗发水' 存货名称,
	1 数量,
	A.`收货人`,
	A.`联系方式1`,
	A.`联系方式2`,
	A.`省`,
	A.`市`,
	A.`区`,
	A.`详细地址`,
	A.`期数`,
	'重消' 订单类型, 
	'01' 发货仓,
	A.`创建时间`,
	A.`支付时间`,
	'' 发货时间,
	0 商品单价,
	0 商品金额,
	0 运费,
	'' 快递公司,
	'' 快递单号,
	'邮寄' 发运方式,
	'赠品' 会员备注,
	'未发货' 发货状态 
FROM


(SELECT
	O.USER_NAME AS 会员编号,
	'Q94839185' AS 报单中心,
	O.SN AS 订单号,
	O.`STATUS` AS 订单状态,
	G.SKU_CODE AS 存货编码,
	G.GOODS_TITLE AS 存货名称,
	G.BUY_NUMS AS 数量,
	O.CONSIGNEE AS 收货人,
	O.MOBILE AS 联系方式1,
	O.TEL AS 联系方式2,
	R.REGION_NAME AS 省,
	R2.REGION_NAME AS 市,
	R3.REGION_NAME AS 区,
	O.ADDRESS AS 详细地址,
	O.PERIOD_NUM AS 期数,
	O.ORDER_TYPE AS 订单类型,
	O.WAREHOUSE AS 发货仓,
	O.CREATED_AT AS 创建时间,
	O.PAY_AT AS 支付时间,
	O.DELIVERY_AT AS 发货时间,
	G.REAL_PRICE AS 商品单价,
	(G.REAL_PRICE * G.BUY_NUMS) AS 商品金额,
	G.REAL_PV AS 商品PV,
	(G.REAL_PV * G.BUY_NUMS) AS PV合计,
	O.PAY_FREIGHT AS 运费,
	O.EXPRESS_COMPANY AS 快递公司,
	O.ORDER_TRACK_NO AS 快递单号,
	O.EXPRESS_TYPE AS 发货方式,
	O.REMARK AS 会员备注,
	O.DELIVERY_STATUS AS 发货状态 
FROM
 AR_ORDER O
 LEFT JOIN AR_ORDER_GOODS G ON G.ORDER_SN = O.SN
 LEFT JOIN AR_REGION R ON R.REGION_CODE = O.PROVINCE
 LEFT JOIN AR_REGION R2 ON R2.REGION_CODE = O.CITY
 LEFT JOIN AR_REGION R3 ON R3.REGION_CODE = O.COUNTY
 WHERE O.CREATED_AT >= UNIX_TIMESTAMP('2021-09-15 09:00:00') AND O.CREATED_AT < UNIX_TIMESTAMP('2021-09-16 09:00:00') AND G.SKU_CODE ='WP32S' AND O.ORDER_TYPE = 'ZC') A
 GROUP BY A.`会员编号`