求助一条关于UNION查询的SQL怎么优化

Question

有三个字段出自同一张表

 SELECT  s.PAY_TYPE,bs.PAY_ORDER AS JY_RC_ORDER,
        **bs.RECEIVE_AMOUNT_FIRST** AS RECEIVE_AMOUNT,
       ** bs.RECEIVE_TIME_FIRST **AS RECEIVE_TIME,
       ** bs.RECEIVE_TYPE_FIRST** AS RECEIVE_TYPE,S.ORDER_NUMBER,S.PAY_AMOUNT,S.PAYMENT_DATE,S.PAY_WAY,s.YXP_AMT 
FROM B2B_BIGSTAGE_SUB s
LEFT JOIN YX_BUYERS bs 
ON s.ORDER_NUMBER=bs.PAY_ORDER
WHERE bs.PAY_ORDER IS NULL AND s.ORDER_NUMBER IS NOT NULL AND s.ORDER_NUMBER LIKE 'JY%' OR s.ORDER_NUMBER LIKE 'RC%'
UNION
SELECT s.PAY_TYPE,bs.PAY_ORDER AS JY_RC_ORDER,
       ** bs.RECEIVE_AMOUNT_SED** as RECEIVE_AMOUNT,
      **  bs.RECEIVE_TIME_SED **AS RECEIVE_TIME,
       ** bs.RECEIVE_TYPE_SED **AS RECEIVE_TYPE,S.ORDER_NUMBER,S.PAY_AMOUNT,S.PAYMENT_DATE,S.PAY_WAY,s.YXP_AMT
FROM B2B_BIGSTAGE_SUB s  
LEFT JOIN YX_BUYERS bs
ON s.ORDER_NUMBER=bs.PAY_ORDER
WHERE bs.PAY_ORDER IS NULL AND s.ORDER_NUMBER IS NOT NULL AND s.ORDER_NUMBER LIKE 'JY%' OR s.ORDER_NUMBER LIKE 'RC%'

Answer 1

从字段意义上猜 RECEIVE_AMOUNT_FIRST 和 RECEIVE_AMOUNT_SED 是不能去重复的，你至少应该是用 UNION ALL 吧，不用去重复查询会快很多。

如果要两个数量合并，相加即可

 SELECT s.PAY_TYPE,bs.PAY_ORDER AS JY_RC_ORDER,
        (ISNULL(bs.RECEIVE_AMOUNT_FIRST,0) + ISNULL(bs.RECEIVE_AMOUNT_SED,0)) AS RECEIVE_AMOUNT,
        ISNULL(bs.RECEIVE_TIME_FIRST, bs.RECEIVE_TIME_SED) AS RECEIVE_TIME,
        ISNULL(bs.RECEIVE_TYPE_FIRST, bs.RECEIVE_TYPE_SED) AS RECEIVE_TYPE,
        S.ORDER_NUMBER,S.PAY_AMOUNT,S.PAYMENT_DATE,S.PAY_WAY,s.YXP_AMT 
FROM B2B_BIGSTAGE_SUB s
LEFT JOIN YX_BUYERS bs 
ON s.ORDER_NUMBER=bs.PAY_ORDER
WHERE bs.PAY_ORDER IS NULL AND s.ORDER_NUMBER IS NOT NULL AND s.ORDER_NUMBER LIKE 'JY%' OR s.ORDER_NUMBER LIKE 'RC%'