sql-server - SQL Server 中的临时表

Question

查询#1:

SELECT a.*  
INTO #TempTable1 
FROM
    (SELECT 
         Vendor,
         CASE WHEN CONVERT(varchar(50), DateModified, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) END AS 'Date of Last Check', 
         CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) END AS 'Date of check for month',
         SUM([Original $ Total]) 'Amount'
     FROM
         Tbl_Commission_Reconcilation  
     WHERE
         Vendor IS NOT NULL
         AND MONTH([Order Date]) = MONTH (GETDATE())
     GROUP BY
         Tbl_Commission_Reconcilation.Vendor,
         CONVERT(varchar(50), DateModified, 101),
         [Sales Rep], PaycommissionDate) a

输出:

Vendor              Date of Last Check   Date of check for month       Amount
-----------------------------------------------------------------------------
Boston Warehouse    12/12/2017            12/12/2017                   919.00
Woodlink            12/12/2017            12/12/2017                   979.86

查询#2:

SELECT b.*  
INTO #TempTable2  
FROM
    (SELECT 
         [Sales Rep],
         SUM([Commission $ paid]) 'Commission $ paid'
     FROM 
         Tbl_Commission_Reconcilation  
     WHERE 
         Vendor IS NOT NULL
         AND MONTH([Order Date]) = MONTH (GETDATE())
     GROUP BY 
         [Sales Rep]) b

输出2:

Sales Rep       Commission $ paid
---------------------------------
Tammy Hanson    379.77

最终查询:

select * 
from #TempTable1, #TempTable2  

最终输出:

Vendor      DateofLastCheck Dateofcheckformonth Amount  Sales Rep      Commpaid
Boston 
Warehouse   12/12/2017     12/12/2017          919.00   Tammy Hanson    379.77
Woodlink    12/12/2017     12/12/2017          979.86   Tammy Hanson    379.77

输出 2 显示只为 Tammy Hanson 支付了一笔佣金 379.77。但最终输出显示支付了 2 倍佣金 379.77。如何纠正此问题？如何在最终输出中只显示一笔佣金？请任何人帮助我。

Answer 1

您获得重复值是因为您在此处使用交叉联接(笛卡尔积)。请指定至少 1 个条件来匹配两个表中的记录，以便仅根据所需的记录显示值。

根据您当前的查询，它将返回两个表中所有可能的行组合。像这样改变它

select * from #TempTable1 T1,#TempTable2  T2 WHERE T1.ColumnName = T2.ColumnName

或者你也可以说

select * from #TempTable1 T1
inner join #TempTable2  T2 
WHERE T1.ColumnName = T2.ColumnName

-- 如果您想要两个表上都有匹配的记录

或

select * from #TempTable1 T1
    left join #TempTable2  T2 
    WHERE T1.ColumnName = T2.ColumnName

-- 如果您想要 #TempTable1 中的记录，并且在没有匹配记录时为 TempTable2 显示 null