不加外采用了wx的日期对比moi的日期取了最大值，数据正常，加外采结果都成了2099了

select ord.wcc_num ,
case when max(wx.min_receipt_date) > max(case when (mop.qr_name not like '%外协%' or mop.department not like '%外采%') then coalesce(moi.min_receipt_date,'2099-12-31') end) then max(wx.min_receipt_date) else max(case when (mop.qr_name not like '%外协%' or mop.department not like '%外采%') then coalesce(moi.min_receipt_date,'2099-12-31') end) end 订单最晚进仓日期,
case when max(wx.min_shipments_date) > max(case when (mop.qr_name not like '%外协%' or mop.department not like '%外采%') then coalesce(moi.min_shipments_date,'2099-12-31') end) then max(wx.min_shipments_date) else max(case when (mop.qr_name not like '%外协%' or mop.department not like '%外采%') then coalesce(moi.min_shipments_date,'2099-12-31') end) end 订单最晚发货日期
from mscs_order_info ord
left join mscs_order_product mop on ord.wcc_num =mop.wcc_num
left join mcts_qrcode_mapping mqm on mqm.qr_name =mop.qr_name
left join mcts_order_info moi on moi.wcc_num =ord.wcc_num and (case when moi.clsleve2='20YQ' then '20YK' when moi.clsleve2='20TM' then '20YL' else moi.clsleve2 end)=mqm.clsleve2
left join mscs_order_shipment_info_wx wx on wx.wcc_num =ord.wcc_num and mop.qr_name =wx.qr_name
left join mscs_cancel_order ca on ord.wcc_num =ca.wcc_num
where 1=1
and mop.is_active =0
and ca.wcc_num is null
and ord.FIRST_SCHEDULE_TIME>=date'2021-01-01'
group by ord.wcc_num

因为它逻辑走到了这里 coalesce(moi.min_receipt_date,'2099-12-31')
然后moi.min_receipt_date为空,自然就是'2099-12-31'了,
而且只要数据中出现了一个这个日期,由于它外层函数是max,最后输出的也会是这个日期了

max(case
                                               when (mop.qr_name not like '%外协%' or mop.department not like '%外采%') then
                                                coalesce(moi.min_receipt_date, '2099-12-31')
                                             end)

这个里面咋没有else？不满足这个条件的全部都空了,一旦为空,你那个大于判断也就失效了。

没明白你这个sql是想查什么,能不能给下create table 和一点insert模拟数据？