主页 / dba / 问题 / 215483
Accepted
Learning_Learning
Asked: 2018-08-22 06:08:03 +0800 CST

如何使用与一年以上关联的文件大小总和获取合并的 ServiceID

  • 772

任何人都可以在下面帮助我获得合并的 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 个查询获得以下输出？请帮助我，我需要加入的方式。我尝试了加入他们的方式，但确定这是错误的，所以寻求帮助。

在此处输入图像描述

谢谢

sql-server t-sql

1 个回答

  1. Best Answer

    这是一个如何做到这一点的例子。

    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          |
    • 1

相关问题