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

有三个字段出自同一张表

 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%'

2个回答

你最好还是告诉别人,你想要查什么。你这sql这么复杂 要么是数据库设计有问题,要么是你sql写的有问题。

从字段意义上猜 RECEIVE_AMOUNT_FIRSTRECEIVE_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%'

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问