时间戳

首页 > 解决方案 > 如何在oracle中使用union进行减法

oracle - 如何在oracle中使用union进行减法

问题描述

SELECT A
FROM   (
        SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) as A --247011
        FROM   RE_STOCK_TB B
        WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
          AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
           IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                        COMPANY_ID, INPUT_NO, STOCK_DATE 
                 FROM   RE_STOCK_TB 
                 WHERE  COMPANY_ID  = B.COMPANY_ID 
                   AND  INPUT_NO    = B.INPUT_NO   
                   AND  STOCK_DATE <= :P_KEY_DATE
                   AND  ROWNUM      = 1
               ) 
          AND  B.RMN_WGT            > 0 
          AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
          AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
        UNION
        SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) --246385
        FROM   RE_STOCK_TB B
        WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
          AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
           IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                        COMPANY_ID, INPUT_NO, STOCK_DATE 
                 FROM   RE_STOCK_TB 
                 WHERE  COMPANY_ID  = B.COMPANY_ID 
                   AND  INPUT_NO    = B.INPUT_NO   
                   AND  STOCK_DATE <= :P_KEY_DATE
                   AND  ROWNUM      = 1
               ) 
          AND  B.RMN_WGT            > 0 
          AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
          AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
          AND  B.SIZE1              IN (0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 1.5, 2.0))

在此处输入图像描述

您好，我正在使用 UNION 并想在 A 列中减去这两个值。有什么办法吗？与 SUM 相反。我在谷歌上看过但找不到任何答案..

标签： oracleplsqlunion

解决方案

有很多方法

一种方法是 * -1 用于联合后选择的列 A

SELECT SUM(A)
FROM   (
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) as A --247011
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
    UNION
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) * -1 --246385
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
      AND  B.SIZE1              IN (0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 
1.5, 2.0))

或者通过添加一个代理列来进行条件总和以进行标记

SELECT SUM(IIF(A = 2, A * -1, A)
FROM   (
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) as A, '1' AS FLAG --247011
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
    UNION
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))), '2' AS FLAG --246385
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
      AND  B.SIZE1              IN (0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 
1.5, 2.0))

推荐阅读