SQL查询单行显示 [英] Sql query show on single line
本文介绍了SQL查询单行显示的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
尝试在单行上显示
目前看起来像
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屋!
查看全文