sql-server - 自联接表和对具有不同条件的同一列求和（SQL Server）

Question

寻求帮助，向我展示如何将以下查询连接在一起，同时在新列中汇总问题和转移的结果。

我试图将它们作为子查询以及内部和外部联接来做，但没有成功。

任何想法或搜索方向将不胜感激。

当前查询和结果：

--30 & 90 day issues query
SELECT 
    Table1.itemnum, SUM(Table1.quantity) AS SumOfquantityIssue
FROM 
    Table1
WHERE 
    Table1.transdate > GETDATE() - 30 
    AND Table1.issuetype = 'ISSUE' 
    AND Table1.gldebitacct IS NOT NULL
    AND Table1.fromstoreloc IN ('WFOWH', 'WFOPY')
GROUP BY 
    Table1.itemnum
ORDER BY 
    Table.itemnum;

我从这个查询中得到的结果：

itemnum   SumOfquantityIssue
----------------------------
  1007       -1.00

第二个查询：

--30 & 90 day transfers query
SELECT 
    Table1.itemnum, SUM(Table1.quantity) AS SumOfquantityTransfer
FROM 
    Table1
WHERE 
    Table1.transdate > GETDATE() - 30 
    AND Table1.issuetype = 'TRANSFER' 
    AND Table1.gldebitacct IS NOT NULL
    AND Table1.fromstoreloc IN ('WFOWH', 'WFOPY')
GROUP BY 
    Table1.itemnum
ORDER BY 
    Table1.itemnum;

我从该查询中得到的结果：

itemnum SumOfquantityTransfer
------------------------------
   1007     1.00
   2347    20.05

我想要得到的结果如下所示：

Itemnum      SumOfquantityIssue   SumOfquantityTransfer  Total_Sum
-------------------------------------------------------------------
 1007             1.00                -1.00                 0.00
 2347            20.05                 0.00                20.05

标签： sql-servermssql-jdbc

Answer 1

您可以像这样在一个查询中执行此操作.. 加入自我.. 我没有在 SSMS 中运行它来检查.. 只是想不通..

    SELECT 
    Table1.itemnum
   , Sum(Table1.quantity) AS SumOfquantityIssue
   , isnull(Sum(Table2.quantity),0) AS SumOfquantityTransfer
   , Sum(Table1.quantity) + isnull(Sum(Table2.quantity),0) as total_sum

    FROM Table1

    LEFT JOIN Table1 as Table2
      ON Table2.itemnum = Table1.itemnum
      AND Table2.issuetype ='TRANSFER' 
      AND Table2.gldebitacct Is Not Null
      AND Table2.fromstoreloc in('WFOWH','WFOPY')
      AND Table2.transdate>getdate()-30 

    WHERE Table1.transdate>getdate()-30 
    AND Table1.issuetype ='ISSUE' 
    AND Table1.gldebitacct Is Not Null
    AND Table1.fromstoreloc in('WFOWH','WFOPY')
    GROUP BY Table1.itemnum
        ORDER BY Table.itemnum;