將 2 個臨時表合併為一個表

我正在研究一個需要大量數據的 SP，將其分成兩個臨時表並將這兩個表聯合在一起。它可以工作，但現在它為每個臨時表提供了不同的行，但我需要將它抓取到一行中。

USE CallDB
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[LDS_ALL_CALLS_120s_PLUS]
AS
BEGIN
end end 



--Pulls all unique calls from CallDB into temp table #tmpTotCalls

IF OBJECT_ID('tempdb..#tmpTotCalls') IS NOT NULL DROP TABLE #tmpTotCalls

SELECT DISTINCT [user],count(*) as [TotCall]
 into #tmpTotCalls
 FROM CallDB
 WHERE status <> 'ALTNUM' --and length_in_sec >= '120'
 GROUP by [user]

--Pulls all unique calls OVER 120 seconds from 
CallDB into temp table #tmp120s

IF OBJECT_ID('tempdb..#tmp120s') IS NOT NULL DROP TABLE #tmp120s

SELECT DISTINCT [user], count(*) as [120calls]
 into #tmp120s
 FROM CallDB
 WHERE status <> 'ALTNUM' and length_in_sec >= '120'
 GROUP by [user]

delete CallDB.[dbo].[120s_calls]
insert into CallDB.[dbo].[120s_calls]

Select [user], TotCall, '0' as [120calls] from #tmpTotCalls 

UNION ALL

select [USER], '0' as [Totcall], [120calls] from #tmp120s

Order by [user]

DROP TABLE #tmp120s
DROP TABLE #tmpTotCalls

select * from CallDB.[dbo].[120s_calls]

END

我得到的解決方案是：

User | totCalls | 120Calls
fernandezv | 0 | 23
fernandezv | 2147 | 0

在我看來，您需要 aJOIN而不是 a UNION：

SELECT  ISNULL(T1.[user],T2.[user]) [user],
       ISNULL(T1.TotCall,0) TotCall,
       ISNULL(T2.[120Calls],0) [120Calls]
FROM #tmpTotCalls T1
FULL JOIN #tmp120s T2
     ON T1.[user] = T2.[user]
;

引用自：https://dba.stackexchange.com/questions/205885