| 1 |
- select `ord`.`会员编号` AS `会员编号`,`ord`.`DEC_ID` AS `DEC_ID`,`ord`.`订单号` AS `订单号`,`ord`.`订单状态` AS `订单状态`,ifnull(convert(`b`.`单品编码` using utf8mb4),`ord`.`存货编码`) AS `存货编码`,ifnull(convert(`b`.`单品名称` using utf8mb4),`ord`.`存货名称`) AS `存货名称`,`ord`.`数量` AS `数量`,`ord`.`收货人` AS `收货人`,`ord`.`联系方式1` AS `联系方式1`,`ord`.`联系方式2` AS `联系方式2`,ifnull(`pro`.`REGION_NAME`,ifnull(convert(`pc`.`province` using utf8mb4),`ord`.`PROVINCE`)) AS `省`,ifnull(`city`.`REGION_NAME`,`ord`.`CITY`) AS `市`,ifnull(`cou`.`REGION_NAME`,`ord`.`COUNTY`) AS `区`,`ord`.`详细地址` AS `详细地址`,`ord`.`期数` AS `期数`,`ord`.`订单类型` AS `订单类型`,`ord`.`发货仓` AS `发货仓`,`ord`.`创建时间` AS `创建时间`,`ord`.`支付时间` AS `支付时间`,`ord`.`发货时间` AS `发货时间`,ifnull(`b`.`单品数量`,`ord`.`数量`) AS `单品数量`,ifnull(((`ord`.`商品单价` * `b`.`单品占比`) / `b`.`单品数量`),`ord`.`商品单价`) AS `商品单价`,ifnull((`ord`.`商品金额` * `b`.`单品占比`),`ord`.`商品金额`) AS `商品金额`,ifnull(((`ord`.`商品PV` * `b`.`单品占比`) / `b`.`单品数量`),`ord`.`商品PV`) AS `商品PV`,ifnull((`ord`.`PV合计` * `b`.`单品占比`),`ord`.`PV合计`) AS `pv合计`,`ord`.`运费` AS `运费`,`ord`.`快递公司` AS `快递公司`,`ord`.`快递单号` AS `快递单号`,`ord`.`发货方式` AS `发货方式`,`ord`.`会员备注` AS `会员备注`,`ord`.`发货状态` AS `发货状态`,concat(`ar_period`.`CALC_YEAR`,lpad(`ar_period`.`CALC_MONTH`,2,0)) AS `月份`,`uu`.`USER_NAME` AS `报单中心`,`ord`.`存货编码` AS `组合品编码`,`ord`.`存货名称` AS `组合品名称`,`ord`.`商品单价` AS `组合品单价`,`ord`.`商品金额` AS `组合品金额`,`ord`.`商品PV` AS `组合品PV`,`ord`.`PV合计` AS `组合品PV合计` from ((((((((select `ar_order_dec`.`USER_NAME` AS `会员编号`,`u`.`DEC_ID` AS `DEC_ID`,`ar_order_goods_dec`.`ORDER_SN` AS `订单号`,`ar_order_dec`.`STATUS` AS `订单状态`,`ar_order_goods_dec`.`SKU_CODE` AS `存货编码`,`ar_order_goods_dec`.`GOODS_TITLE` AS `存货名称`,`ar_order_goods_dec`.`BUY_NUMS` AS `数量`,`ar_order_dec`.`CONSIGNEE` AS `收货人`,`ar_order_dec`.`MOBILE` AS `联系方式1`,`ar_order_dec`.`TEL` AS `联系方式2`,`ar_order_dec`.`PROVINCE` AS `PROVINCE`,`ar_order_dec`.`CITY` AS `CITY`,`ar_order_dec`.`COUNTY` AS `COUNTY`,`ar_order_dec`.`ADDRESS` AS `详细地址`,`ar_order_dec`.`PERIOD_NUM` AS `期数`,(case when ((`ar_order_dec`.`ORDER_TYPE` = 'FX') and (`ar_order_dec`.`PAY_TYPE` <> 'point')) then '复消' when ((`ar_order_dec`.`ORDER_TYPE` = 'ZC') and (`ar_order_dec`.`PAY_TYPE` <> 'point')) then '报单' when (`ar_order_dec`.`PAY_TYPE` = 'point') then '积分' else `ar_order_dec`.`ORDER_TYPE` end) AS `订单类型`,`ar_order_dec`.`WAREHOUSE` AS `发货仓`,date_format(from_unixtime(`ar_order_dec`.`CREATED_AT`),'%Y-%m-%d %H:%i:%s') AS `创建时间`,date_format(from_unixtime(`ar_order_dec`.`PAY_AT`),'%Y-%m-%d %H:%i:%s') AS `支付时间`,date_format(from_unixtime(`ar_order_dec`.`DELIVERY_AT`),'%Y-%m-%d %H:%i:%s') AS `发货时间`,(`ar_order_goods_dec`.`REAL_PRICE` / `ar_order_goods_dec`.`BUY_NUMS`) AS `商品单价`,`ar_order_goods_dec`.`REAL_PRICE` AS `商品金额`,(`ar_order_goods_dec`.`REAL_PV` / `ar_order_goods_dec`.`BUY_NUMS`) AS `商品PV`,`ar_order_goods_dec`.`REAL_PV` AS `PV合计`,`ar_order_dec`.`FREIGHT` AS `运费`,`ar_order_dec`.`EXPRESS_COMPANY` AS `快递公司`,`ar_order_dec`.`ORDER_TRACK_NO` AS `快递单号`,`ar_order_dec`.`EXPRESS_TYPE` AS `发货方式`,`ar_order_dec`.`FRONT_REMARK` AS `会员备注`,`ar_order_dec`.`DELIVERY_STATUS` AS `发货状态` from ((`ar_order_goods_dec` join `ar_order_dec`) join `ar_user` `u`) where ((`ar_order_goods_dec`.`ORDER_SN` = `ar_order_dec`.`SN`) and (`ar_order_dec`.`USER_ID` = `u`.`ID`) and (`ar_order_dec`.`PERIOD_NUM` >= 120)) union all select `ar_order_shop`.`USER_NAME` AS `会员编号`,`u`.`DEC_ID` AS `DEC_ID`,`ar_order_goods_shop`.`ORDER_SN` AS `订单号`,`ar_order_shop`.`STATUS` AS `订单状态`,`ar_order_goods_shop`.`SKU_CODE` AS `存货编码`,`ar_order_goods_shop`.`GOODS_TITLE` AS `存货名称`,`ar_order_goods_shop`.`BUY_NUMS` AS `数量`,`ar_order_shop`.`CONSIGNEE` AS `收货人`,`ar_order_shop`.`MOBILE` AS `联系方式1`,`ar_order_shop`.`TEL` AS `联系方式2`,`ar_order_shop`.`PROVINCE` AS `PROVINCE`,`ar_order_shop`.`CITY` AS `CITY`,`ar_order_shop`.`COUNTY` AS `COUNTY`,`ar_order_shop`.`ADDRESS` AS `详细地址`,`ar_order_shop`.`PERIOD_NUM` AS `期数`,(case when ((`ar_order_shop`.`ORDER_TYPE` = 'FX') and (`ar_order_shop`.`PAY_TYPE` <> 'point')) then '复消' when ((`ar_order_shop`.`ORDER_TYPE` = 'ZC') and (`ar_order_shop`.`PAY_TYPE` <> 'point')) then '报单' when (`ar_order_shop`.`PAY_TYPE` = 'point') then '积分' else `ar_order_shop`.`ORDER_TYPE` end) AS `订单类型`,`ar_order_shop`.`WAREHOUSE` AS `发货仓`,date_format(from_unixtime(`ar_order_shop`.`CREATED_AT`),'%Y-%m-%d %H:%i:%s') AS `创建时间`,date_format(from_unixtime(`ar_order_shop`.`PAY_AT`),'%Y-%m-%d %H:%i:%s') AS `支付时间`,date_format(from_unixtime(`ar_order_shop`.`DELIVERY_AT`),'%Y-%m-%d %H:%i:%s') AS `发货时间`,(`ar_order_goods_shop`.`REAL_PRICE` / `ar_order_goods_shop`.`BUY_NUMS`) AS `商品单价`,`ar_order_goods_shop`.`REAL_PRICE` AS `商品金额`,(`ar_order_goods_shop`.`REAL_PV` / `ar_order_goods_shop`.`BUY_NUMS`) AS `商品PV`,`ar_order_goods_shop`.`REAL_PV` AS `PV合计`,`ar_order_shop`.`FREIGHT` AS `运费`,`ar_order_shop`.`EXPRESS_COMPANY` AS `快递公司`,`ar_order_shop`.`ORDER_TRACK_NO` AS `快递单号`,`ar_order_shop`.`EXPRESS_TYPE` AS `发货方式`,`ar_order_shop`.`FRONT_REMARK` AS `会员备注`,`ar_order_shop`.`DELIVERY_STATUS` AS `发货状态` from ((`ar_order_goods_shop` join `ar_order_shop`) join `ar_user` `u`) where ((`ar_order_goods_shop`.`ORDER_SN` = `ar_order_shop`.`SN`) and (`ar_order_shop`.`USER_ID` = `u`.`ID`) and (`ar_order_shop`.`PERIOD_NUM` >= 120)) union all select `ar_order`.`USER_NAME` AS `会员编号`,`u`.`DEC_ID` AS `DEC_ID`,`ar_order_goods`.`ORDER_SN` AS `订单号`,`ar_order`.`STATUS` AS `订单状态`,`ar_order_goods`.`SKU_CODE` AS `存货编码`,`ar_order_goods`.`GOODS_TITLE` AS `存货名称`,`ar_order_goods`.`BUY_NUMS` AS `数量`,`ar_order`.`CONSIGNEE` AS `收货人`,`ar_order`.`MOBILE` AS `联系方式1`,`ar_order`.`TEL` AS `联系方式2`,`ar_order`.`PROVINCE` AS `PROVINCE`,`ar_order`.`CITY` AS `CITY`,`ar_order`.`COUNTY` AS `COUNTY`,`ar_order`.`ADDRESS` AS `详细地址`,`ar_order`.`PERIOD_NUM` AS `期数`,(case when ((`ar_order`.`ORDER_TYPE` = 'FX') and (`ar_order`.`PAY_TYPE` <> 'point')) then '复消' when ((`ar_order`.`ORDER_TYPE` = 'ZC') and (`ar_order`.`PAY_TYPE` <> 'point')) then '报单' when (`ar_order`.`PAY_TYPE` = 'point') then '积分' else `ar_order`.`ORDER_TYPE` end) AS `订单类型`,`ar_order`.`WAREHOUSE` AS `发货仓`,date_format(from_unixtime(`ar_order`.`CREATED_AT`),'%Y-%m-%d %H:%i:%s') AS `创建时间`,date_format(from_unixtime(`ar_order`.`PAY_AT`),'%Y-%m-%d %H:%i:%s') AS `支付时间`,date_format(from_unixtime(`ar_order`.`DELIVERY_AT`),'%Y-%m-%d %H:%i:%s') AS `发货时间`,`ar_order_goods`.`REAL_PRICE` AS `商品单价`,(`ar_order_goods`.`REAL_PRICE` * `ar_order_goods`.`BUY_NUMS`) AS `商品金额`,`ar_order_goods`.`REAL_PV` AS `商品PV`,(`ar_order_goods`.`REAL_PV` * `ar_order_goods`.`BUY_NUMS`) AS `PV合计`,`ar_order`.`FREIGHT` AS `运费`,`ar_order`.`EXPRESS_COMPANY` AS `快递公司`,`ar_order`.`ORDER_TRACK_NO` AS `快递单号`,`ar_order`.`EXPRESS_TYPE` AS `发货方式`,`ar_order`.`FRONT_REMARK` AS `会员备注`,`ar_order`.`DELIVERY_STATUS` AS `发货状态` from ((`ar_order_goods` join `ar_order`) join `ar_user` `u`) where ((`ar_order_goods`.`ORDER_SN` = `ar_order`.`SN`) and (`ar_order`.`USER_ID` = `u`.`ID`) and (`ar_order`.`PERIOD_NUM` >= 120))) `ord` left join `ar_period` on((`ar_period`.`PERIOD_NUM` = `ord`.`期数`))) left join `ar_user` `uu` on((`ord`.`DEC_ID` = `uu`.`ID`))) left join `组合品信息` `b` on((`ord`.`存货编码` = convert(`b`.`组合编码` using utf8mb4)))) left join `ar_region` `pro` on((`ord`.`PROVINCE` = `pro`.`REGION_CODE`))) left join `ar_region` `city` on((`ord`.`CITY` = `city`.`REGION_CODE`))) left join `ar_region` `cou` on((`ord`.`COUNTY` = `cou`.`REGION_CODE`))) left join `zr_province_contrast` `pc` on((`ord`.`PROVINCE` = convert(`pc`.`orig_province` using utf8mb4))))
|
