在新表中显示单行中的所有表

Question

在SQL Server中，我编写了一个查询来计算特定列，但我在多个表中显示所有计数，这不是显示的好方法。如何在新表中单行显示所有表格？

     select COUNT(s.AssetSubType) as 'PhysicalServers' from Asset s
      where s.CompanyId = @companyId and
            s.AssetType = 1 and
            s.AssetSubType = 4 
   select COUNT(s.AssetSubType) as 'WorkStations' from Asset s
      where s.CompanyId = @companyId and
            s.AssetType = 1 and
            s.AssetSubType = 1 or s.AssetSubType = 3    
 select COUNT(s.AssetSubType) as 'EmailOnlyUsers' from Asset s
      where s.CompanyId = @companyId and
            s.AssetType = 2 and 
            s.AssetSubType = 16             
 select COUNT(s.OperatingSystem) as '#OfMSServers' from Asset s
      where s.CompanyId = @companyId and
            s.AssetType = 1 and
            s.AssetSubType = 4 and 
            s.OperatingSystem = 1 
 select COUNT(s.OperatingSystem) as '#OfLinuxServers' from Asset s
      where s.CompanyId = @companyId and
            s.AssetType = 1 and
            s.AssetSubType = 4 and 
            s.OperatingSystem = 2   

Answer 1

这样的东西应该适合你

select sum(case when s.AssetType = 1 and 
                     s.AssetSubType = 4 
                then 1 end) as 'PhysicalServers',
       sum(case when s.AssetType = 1 and 
                     (s.AssetSubType = 1 or s.AssetSubType = 3) 
                then 1 end) as 'WorkStations',
       sum(case when s.AssetType = 2 and 
                     s.AssetSubType = 16 
                then 1 end) as 'EmailOnlyUsers',
       sum(case when s.AssetType = 1 and 
                     s.AssetSubType = 4 and 
                     s.OperatingSystem = 1 
                then 1 end) as '#OfMSServers',
       sum(case when s.AssetType = 1 and 
                     s.AssetSubType = 4 and 
                     s.OperatingSystem = 2 
                then 1 end) as '#OfLinuxServers'
from Asset s
where s.CompanyId = @companyId

Answer 2

您可以创建一个计算列"serverType"，该列对每种配置类型都有一个唯一的值：

ALTER TABLE [Asset]
 ADD [serverType] AS (
    CASE 
     WHEN (AssetType = 1 and AssetSubType = 4) THEN 'PhysicalServers'
     WHEN (AssetType = 1 and AssetSubType = 1 AssetSubType = 3) THEN 'WorkStations'
     [etc..]
     ELSE 'Unknown'
    END
) 

然后你可以简单地查询

SELECT COUNT(s.OperatingSystem) FROM Asset s
WHERE s.CompanyId = @companyId 
GROUP BY serverType