sql-server - 如何将一个表上的多个 SQL 查询合并到一个报告中

Question

我查看了子查询、联合和联接。它们似乎无法产生我正在寻找的东西，或者我没有正确使用它们。

我使用的是 SQL Server 2016。

这是我的。一个表 (StoreScan)，其中一行表示商店编号，然后是一堆其他行，其中包含每个商店的收集数据。

示例:

StoreNumber, uptime, service1status, service2status

示例数据:

Storenumber     uptime    service1status    service2status  
-----------------------------------------------------------
 1              10        Running           Running
 18             25        Running           Stopped 
 88             3         Stopped           Running
 90             1         Running           Running
 103            5         Stopped           Running
 553            2         Running           Stopped
 989            2         Running           Running

我有几个像这样的小查询:

select storenumber, uptime 
from storescan 
where uptime > 7


select storenumber, service1status 
from storescan 
where service1status = 'stopped'

我想将它们全部合并到一个查询中，以获得如下所示的报告:

Storenumber     uptime    service1status    service2status  
-----------------------------------------------------------
1               10  
18              25  
88                        stopped  
103                       stopped  
553                                         stopped  
18                                          stopped 

Answer 1

进行条件聚合:

select Storenumber, 
       max(case when uptime > 7 then uptime end) as uptime,
       max(case when service1status = 'stopped' then service1status end),
       max(case when service2status = 'stopped' then service2status end)
from storescan ss 
group by Storenumber;