weixin_36440307
启源
采纳率0%
2017-03-31 04:14 阅读 1.1k

求助一条关于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条回答 默认 最新

  • songqiang2011 像忘了谁sq 2017-03-31 06:39

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

    点赞 评论 复制链接分享
  • Tiger_Zhao Tiger_Zhao 2017-03-31 09:14

    从字段意义上猜 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%'
    
    
    点赞 评论 复制链接分享

相关推荐