下面是我为从SCCM数据库获取数据而创建的查询。为了获得更多的细节,我遇到了一点麻烦。
select UI.ArticleID ,ui.DateLastModified,
SUM (CaSe WHEN (AAA.StateID = '2') Then 1 else 0
END) As Install_Count,
sum ( CASE
WHEN ((AAA.StateID = '2') and ( OPSYS.Caption0 like 'Microsoft Windows%10%')) THEN 1 else 0
END ) As 'W10',
sum ( CASE
WHEN ((AAA.StateID = '2') and (OPSYS.Caption0 = 'Microsoft Windows 7 Enterprise' or OPSYS.Caption0 = 'Microsoft Windows 7 Entreprise')) THEN 1 else 0
END ) As 'W7',
sum ( CASE
WHEN ((AAA.StateID != '2') and ((OPSYS.Caption0 not like 'Microsoft Windows%10%') and (OPSYS.Caption0 != 'Microsoft Windows 7 Enterprise') and (OPSYS.Caption0 != 'Microsoft Windows 7 Entreprise'))) THEN 1 else 0
END ) As 'Other',
count(AAA.ResourceID) As 'Total Machines'
FROM v_UpdateInfo UI
INNER JOIN v_CIAssignmentToCI CIA ON UI.CI_ID = CIA.CI_ID
INNER JOIN v_CIAssignment ON CIA.AssignmentID = v_CIAssignment.AssignmentID
Inner Join v_AssignmentStatePerTopic AAA on AAA.AssignmentID = CIA.AssignmentID
inner join v_GS_OPERATING_SYSTEM OPSYS on OPSYS.ResourceID=AAA.ResourceID
where CIA.AssignmentID = '1234567' and aaa.TopicType = '302'
Group By ui.Title, UI.ArticleID ,ui.DateLastModified
上述查询的输出。
ArticleID DateLastModified Install_Count W10 W7 Other Total Machines
4519998 2019-10-09 02:19:43.000 26 23 3 0 28
4520010 2019-10-09 02:18:04.000 26 23 3 0 28
4520004 2019-10-09 02:20:05.000 26 23 3 0 28
正如您在输出中看到的,Other
显示为0。由于Total machines
是28,所以我希望Other
部分有2台机器。我稍后会调查。
我想从Total Machines中获取Install_Count的百分比(以十进制表示(。
尝试以下片段:
((Install_Count * 100) / Total Machines) AS Total_Percentage
代码实现:
select UI.ArticleID ,ui.DateLastModified,
SUM (CaSe WHEN (AAA.StateID = '2') Then 1 else 0
END) As Install_Count,
sum ( CASE
WHEN ((AAA.StateID = '2') and ( OPSYS.Caption0 like 'Microsoft Windows%10%')) THEN 1 else 0
END ) As 'W10',
sum ( CASE
WHEN ((AAA.StateID = '2') and (OPSYS.Caption0 = 'Microsoft Windows 7 Enterprise' or OPSYS.Caption0 = 'Microsoft Windows 7 Entreprise')) THEN 1 else 0
END ) As 'W7',
sum ( CASE
WHEN ((AAA.StateID != '2') and ((OPSYS.Caption0 not like 'Microsoft Windows%10%') and (OPSYS.Caption0 != 'Microsoft Windows 7 Enterprise') and (OPSYS.Caption0 != 'Microsoft Windows 7 Entreprise'))) THEN 1 else 0
END ) As 'Other',
count(AAA.ResourceID) As 'Total Machines',
((SUM (CaSe WHEN (AAA.StateID = '2') Then 1 else 0
END) *100)/count(AAA.ResourceID)) AS Total_percentage
FROM v_UpdateInfo UI
INNER JOIN v_CIAssignmentToCI CIA ON UI.CI_ID = CIA.CI_ID
INNER JOIN v_CIAssignment ON CIA.AssignmentID = v_CIAssignment.AssignmentID
Inner Join v_AssignmentStatePerTopic AAA on AAA.AssignmentID = CIA.AssignmentID
inner join v_GS_OPERATING_SYSTEM OPSYS on OPSYS.ResourceID=AAA.ResourceID
where CIA.AssignmentID = '1234567' and aaa.TopicType = '302'
Group By ui.Title, UI.ArticleID ,ui.DateLastModified
编辑:
select UI.ArticleID ,ui.DateLastModified,
SUM (CaSe WHEN (AAA.StateID = '2') Then 1 else 0
END) As Install_Count,
sum ( CASE
WHEN ((AAA.StateID = '2') and ( OPSYS.Caption0 like 'Microsoft Windows%10%')) THEN 1 else 0
END ) As 'W10',
sum ( CASE
WHEN ((AAA.StateID = '2') and (OPSYS.Caption0 = 'Microsoft Windows 7 Enterprise' or OPSYS.Caption0 = 'Microsoft Windows 7 Entreprise')) THEN 1 else 0
END ) As 'W7',
sum ( CASE
WHEN ((AAA.StateID != '2') and ((OPSYS.Caption0 not like 'Microsoft Windows%10%') and (OPSYS.Caption0 != 'Microsoft Windows 7 Enterprise') and (OPSYS.Caption0 != 'Microsoft Windows 7 Entreprise'))) THEN 1 else 0
END ) As 'Other',
count(AAA.ResourceID) As 'Total Machines',
cast(((SUM (CaSe WHEN (AAA.StateID = '2') Then 1 else 0
END) *100)/count(AAA.ResourceID))as decimal(10,2)) AS Total_percentage
FROM v_UpdateInfo UI
INNER JOIN v_CIAssignmentToCI CIA ON UI.CI_ID = CIA.CI_ID
INNER JOIN v_CIAssignment ON CIA.AssignmentID = v_CIAssignment.AssignmentID
Inner Join v_AssignmentStatePerTopic AAA on AAA.AssignmentID = CIA.AssignmentID
inner join v_GS_OPERATING_SYSTEM OPSYS on OPSYS.ResourceID=AAA.ResourceID
where CIA.AssignmentID = '1234567' and aaa.TopicType = '302'
Group By ui.Title, UI.ArticleID ,ui.DateLastModified