博客

SELECT
a.CONTRACT_BILLCODE as 订单号, '' as 退款单号,
c.brandname,
b.userinfoCompname as 会员姓名,
b.usercard as 会员号,
b.md as 门店,
b.userstoreCode 门店编码,
a.GOODS_NUM as 商品数量,
a.DATA_BMONEY as 订单金额,
case a.DATA_STATE
when 2 then '待发货'
when 3 then '已发货'
when 4 then '已收货'
when 5 then '交易成功'
when -1 then '已取消'
end  as 订单状态,
a.GMT_CREATE as 订单时间,a.CONTRACT_PAYDATE as 支付时间
FROM `ods_oc_contract` a
join obc_plt_channel c on a.MEMBER_MCODE=c.userinfo_code
left join ods_cdp_userinfo_txtend b on a.MEMBER_BCODE = b.USERINFO_CODE and b.CHANNEL_CODE=c.channel_code
where
a.DATA_STATE in ('2','3','4','5')
and a.CONTRACT_PAYDATE >='2022-12-01'
UNION
SELECT
a.CONTRACT_BILLCODE as 订单号, '' as 退款单号,
c.brandname,
b.userinfoCompname as 会员姓名,
b.usercard as 会员号,
b.md as 门店,
b.userstoreCode 门店编码,
a.GOODS_NUM as 商品数量,
a.DATA_BMONEY as 订单金额,
case a.DATA_STATE
when 2 then '待发货'
when 3 then &#