如何獲得與一年多相關的文件大小總和的綜合 ServiceID

任何人都可以在下面幫助我獲得整合的 ServiceID 以及一年內與之相關的文件大小的總和。

完整程式碼（供參考）

If(OBJECT_ID('tempdb..#temp1') Is Not Null)
Begin
   Drop Table #Temp1
End

GO

If(OBJECT_ID('tempdb..#temp2') Is Not Null)
Begin
   Drop Table #Temp2
End
GO

If(OBJECT_ID('tempdb..#temp3') Is Not Null)
Begin
   Drop Table #Temp3
End

GO

If(OBJECT_ID('tempdb..#temp4') Is Not Null)
Begin
   Drop Table #Temp4
End
GO

SELECT *
INTO #Temp1
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
     , FileTotalSize as FileSize 
     ,year = '1980' from table1 
   ) a

--1981
SELECT *
INTO #Temp2
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
     , FileTotalSize as FileSize 
     ,year = '1981'
       from table2 
   ) b

--1982
SELECT *
INTO #Temp3
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
     , FileTotalSize as FileSize
     ,year = '1982' from table3
) c

--1983

SELECT *
INTO #Temp4
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
     , FileTotalSize as FileSize 
     ,year = '1983' from table4 
   ) d


select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

以下是我最終工作的#temp 表中存在的 4 個查詢及其輸出：

select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

上述 4 個查詢的輸出：

**我的問題：**如何使用上述 4 個查詢獲得以下輸出？請幫幫我，我需要加入的方式。我嘗試了加入他們的方式，但確定這是錯誤的，因此尋求幫助。

謝謝

這是一個如何做到這一點的例子。

Declare @Query1 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query2 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query3 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query4 table (id int, ServiceID int, Year int, FileSize1 int)

insert into @Query1(id,ServiceID,Year,FileSize1) values
(1,121,1980,10),
(2,122,1980,20),
(3,123,1980,30),
(4,121,1980,40)

insert into @Query2(id,ServiceID,Year,FileSize1) values
(1,121,1981,10),
(2,122,1981,20),
(3,123,1981,30),
(4,123,1981,40)

insert into @Query3(id,ServiceID,Year,FileSize1) values
(1,121,1982,10),
(2,122,1982,20),
(3,124,1982,30),
(4,125,1982,40)

insert into @Query4(id,ServiceID,Year,FileSize1) values
(1,123,1983,10),
(2,121,1983,20),
(3,122,1983,30),
(4,121,1983,40)

;With _cte as
(
select ServiceID,Year,FileSize1 as FileSize1 from @Query1 
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query2 
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query3 
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query4 
)

select ServiceID,
sum(case when year = 1980 then FileSize1 end) as FileSize_1980,
sum(case when year = 1981 then FileSize1 end) as FileSize_1981,
sum(case when year = 1982 then FileSize1 end) as FileSize_1982,
sum(case when year = 1983 then FileSize1 end) as FileSize_1983
from _cte
group by ServiceID

---

| ServiceID | FileSize_1980 | FileSize_1981 | FileSize_1982 | FileSize_1983 |
|-----------|---------------|---------------|---------------|---------------|
| 121       | 50            | 10            | 10            | 60            |
| 122       | 20            | 20            | 20            | 30            |
| 123       | 30            | 70            | NULL          | 10            |
| 124       | NULL          | NULL          | 30            | NULL          |
| 125       | NULL          | NULL          | 40            | NULL          |

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