使用UNION ALL子句将两个单独的查询求和为一个值

Question

我有以下查询(作为更大查询的一部分)。我试图从子查询中的两个不同的数据集获取求和结果，但在尝试将这两个数据集封装为1个值时遇到了问题。我所拥有的是：

(Select SUM('Invoiced MTD') from 

    ((Select SUM(CASE WHEN SOH.LASDLVNUM_0 <> '' AND SOH.LASINVNUM_0 <> '' AND MONTH(SOH.SHIDAT_0) = MONTH(GETDATE()) THEN 
    (SOP.NETPRI_0 * SOQ.QTY_0 * SOH.CHGRAT_0) ELSE 0 END) as 'Invoiced MTD' 
    From x3v6.CICPROD.SORDER SOH
    LEFT OUTER JOIN x3v6.CICPROD.BPCUSTOMER BPC on SOH.BPCORD_0 = BPC.BPCNUM_0
    LEFT OUTER JOIN x3v6.CICPROD.SORDERQ SOQ on SOH.SOHNUM_0 = SOQ.SOHNUM_0
    LEFT OUTER JOIN x3v6.CICPROD.SORDERP SOP on SOQ.SOHNUM_0 = SOP.SOHNUM_0 and SOQ.SOPLIN_0 = SOP.SOPLIN_0 and SOQ.SOQSEQ_0 = SOP.SOPSEQ_0
    LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER ITM on SOP.ITMREF_0 = ITM.ITMREF_0 ))

    UNION ALL

    ((Select SUM(CASE WHEN SIH.INVTYP_0 = 2 and MONTH(SIH.ACCDAT_0) = MONTH(GETDATE()) THEN SID.AMTNOTLIN_0 * (-1) ELSE 0 END) as 'Invoiced MTD'
    From x3v6.CICPROD.SINVOICE SIH 
    Left Outer Join x3v6.CICPROD.SINVOICED SID on SIH.NUM_0 = SID.NUM_0))

 as 'T2',

但是我在UNION ALL子句所在的位置遇到错误，并且我找不到它。基本上，我希望将销售业绩通知单与一个单独的表中的销售订单美元总额结合起来。

有人能帮我解决这个问题吗？

Answer 1

那这个呢？

Select SUM([Invoiced MTD]) from
(  
  Select SUM(CASE WHEN SOH.LASDLVNUM_0 <> '' AND SOH.LASINVNUM_0 <> '' AND MONTH(SOH.SHIDAT_0) = MONTH(GETDATE()) 
                   THEN (SOP.NETPRI_0 * SOQ.QTY_0 * SOH.CHGRAT_0) ELSE 0 END) as 'Invoiced MTD' 
   From x3v6.CICPROD.SORDER SOH
   LEFT OUTER JOIN x3v6.CICPROD.BPCUSTOMER BPC on SOH.BPCORD_0 = BPC.BPCNUM_0
   LEFT OUTER JOIN x3v6.CICPROD.SORDERQ SOQ on SOH.SOHNUM_0 = SOQ.SOHNUM_0
   LEFT OUTER JOIN x3v6.CICPROD.SORDERP SOP on SOQ.SOHNUM_0 = SOP.SOHNUM_0 and SOQ.SOPLIN_0 = SOP.SOPLIN_0 and SOQ.SOQSEQ_0 = SOP.SOPSEQ_0
   LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER ITM on SOP.ITMREF_0 = ITM.ITMREF_0 

UNION ALL
   Select SUM(CASE WHEN SIH.INVTYP_0 = 2 and MONTH(SIH.ACCDAT_0) = MONTH(GETDATE()) 
                THEN SID.AMTNOTLIN_0 * (-1) ELSE 0 END) as 'Invoiced MTD'
   From x3v6.CICPROD.SINVOICE SIH 
   Left Outer Join x3v6.CICPROD.SINVOICED SID on SIH.NUM_0 = SID.NUM_0
)T

Answer 2

这行得通吗？我不确定到底是什么导致了你的问题，但你绝对不需要这么多的括号。我还建议使用一些可以格式化/美化SQL的东西。这是一个很好的方式来1)保持你的代码看起来一致，2)充实语法错误。

SELECT SUM(x.invoiced_mtd)
  FROM (SELECT SUM(CASE
                     WHEN soh.lasdlvnum_0 <> '' AND soh.lasinvnum_0 <> '' AND
                          MONTH(soh.shidat_0) = MONTH(getdate()) THEN
                      (sop.netpri_0 * soq.qty_0 * soh.chgrat_0)
                     ELSE
                      0
                   END) AS invoiced_mtd
          FROM x3v6.cicprod.sorder soh
          LEFT OUTER JOIN x3v6.cicprod.bpcustomer bpc
            ON soh.bpcord_0 = bpc.bpcnum_0
          LEFT OUTER JOIN x3v6.cicprod.sorderq soq
            ON soh.sohnum_0 = soq.sohnum_0
          LEFT OUTER JOIN x3v6.cicprod.sorderp sop
            ON soq.sohnum_0 = sop.sohnum_0
           AND soq.soplin_0 = sop.soplin_0
           AND soq.soqseq_0 = sop.sopseq_0
          LEFT OUTER JOIN x3v6.cicprod.itmmaster itm
            ON sop.itmref_0 = itm.itmref_0
        UNION ALL
        SELECT SUM(CASE
                     WHEN sih.invtyp_0 = 2 AND
                          MONTH(sih.accdat_0) = MONTH(getdate()) THEN
                      sid.amtnotlin_0 * (-1)
                     ELSE
                      0
                   END)
          FROM x3v6.cicprod.sinvoice sih
          LEFT OUTER JOIN x3v6.cicprod.sinvoiced sid
            ON sih.num_0 = sid.num_0) x;

Answer 3

请先尝试对UNION查询使用CTE。下面是一个与您的查询具有相同结构的简化示例：

;with cteTest AS (

    ((select 2 as 'test'))
    union all
    ((select 3 as 'test'))

)

select sum(test) from cteTest