SQL查询单行显示 [英] Sql query show on single line

问题描述

尝试在单行上显示

目前看起来像

Trying to show on single row

Currently looks like

MS18-03-W10 NULL 5526
MS18-03-W10 24249 NULL

想要

MS18-03-W10 24249 5526

我尝试过的事情:

What I have tried:

Select Distinct p.name
,Case
When i.installed = '1' then count (c.name)
End 
,Case
When i.installed = '0' then count (c.name)
End 
From vRM_Software_Bulletin_Item_ExactType p
left join vPMWindows_ComplianceReportsDrilldown i on i.SoftwareBulletin = p.Guid
left Join vcomputer  c on c.guid = i._ResourceGuid
Where 
i.ReleaseDate > '2017'
AND
i.BulletinState = '1'
and
p.name = 'MS18-03-W10'
group by p.name, i.Installed

推荐答案

尝试以下操作:

Try this:

SELECT p.name,
       SUM(CASE WHEN i.installed = '1' THEN 1 ELSE 0 END) AS counter
FROM   vRM_Software_Bulletin_Item_ExactType AS p
LEFT JOIN vPMWindows_ComplianceReportsDrilldown AS i ON i.SoftwareBulletin = p.Guid
LEFT JOIN vcomputer AS c ON c.guid = i._ResourceGuid
WHERE i.ReleaseDate > '2017'
AND   i.BulletinState = '1'
AND   p.name = 'MS18-03-W10'
GROUP BY p.name, i.Installed 

次要小调-我喜欢将所有SQL保留字全部大写，以便它们更好地显示.指定别名时，也会使用"AS"，因为这是一种很好的习惯.

此外，如果您正在使用GROUP BY，则不需要DISTINCT.

Minor nit - I like to make all the SQL reserved words all-caps so they stand out a little better. I also use "AS" when I''m specifying aliases, because it''s just a good habit to get into.

Furthermore, you don''t need DISTINCT if you''re using GROUP BY.

关闭但不要退出我正在寻找的

我需要排成一排

Close but not quit what I am looking for

I need to have on one row

Bulletin Name 	Installed	Not Installed
MS18-03-W10	17952	        563
MS18-03-MR7	18563	        2056

Select Distinct 
        p.name
       ,SUM(Case When i.installed = '1' then 1 End)
           OVER(PARTITION BY p.name ORDER BY p.name) AS [Installed]	
       ,SUM(Case When i.installed = '0' then  1 End)
           OVER(PARTITION BY p.name ORDER BY p.name) AS [Not Installed] 

  From vRM_Software_Bulletin_Item_ExactType p

  left join vPMWindows_ComplianceReportsDrilldown i 
       on i.SoftwareBulletin = p.Guid
  left Join vcomputer c on c.guid = i._ResourceGuid

 Where 

 i.ReleaseDate > '2017'
 AND
 i.BulletinState = '1'

这篇关于SQL查询单行显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！