退换货订单union合并，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 '已发货'
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 '已发货'
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 =-1 and a.REFUND_MONEY > 1
and a.CONTRACT_PAYDATE >='2022-12-01'
union
SELECT
aa.CONTRACT_BILLCODE as 订单号,aa.REFUND_CODE as 退款单号,
cc.brandname,
bb.userinfoCompname as 会员姓名,
bb.usercard as 会员号,
bb.md as 门店,
bb.userstoreCode 门店编码,
aa.GOODS_NUM*-1 as 商品数量,
aa.REFUND_MONEY *-1 as 订单金额,
case aa.DATA_STATE
when 8 then '已完成'
when 0 then '待审核'
when -1 then '已取消'
end  as 订单状态,
aa.GMT_CREATE as 订单时间,aa.refund_date as 支付时间
FROM obc_oc_refund aa
join obc_plt_channel cc on aa.MEMBER_MCODE=cc.userinfo_code
left join ods_cdp_userinfo_txtend bb on aa.MEMBER_BCODE = bb.USERINFO_CODE and bb.CHANNEL_CODE=cc.channel_code
where
aa.DATA_STATE = 8 and aa.REFUND_MONEY >1
and aa.refund_date >='2022-12-01' and aa.GOODS_CLASS='pos'
union
SELECT
aa.CONTRACT_BILLCODE as 订单号,aa.REFUND_CODE as 退款单号,
cc.brandname,
bb.userinfoCompname as 会员姓名,
bb.usercard as 会员号,
bb.md as 门店,
bb.userstoreCode 门店编码,
aa.GOODS_NUM*-1 as 商品数量,
aa.REFUND_MONEY *-1 as 订单金额,  /*求和的时候订单金额减去退货金额，正好是正常的金额*/
case aa.DATA_STATE
when 8 then '已完成'
when 0 then '待审核'
when -1 then '已取消'
end  as 订单状态,
aa.GMT_CREATE as 订单时间,aa.GMT_MODIFIED as 支付时间   /*由于线上的refund_date为空所以改为modifytime*/
FROM obc_oc_refund aa
join obc_plt_channel cc on aa.MEMBER_MCODE=cc.userinfo_code
left join ods_cdp_userinfo_txtend bb on aa.MEMBER_BCODE = bb.USERINFO_CODE and bb.CHANNEL_CODE=cc.channel_code
where
aa.DATA_STATE = 8 and aa.REFUND_MONEY >1
and aa.GMT_MODIFIED >='2022-12-01' and aa.GOODS_CLASS<> 'pos';