问按主要版本列出软件的SQL查询
EN

Database Administration用户

提问于 2015-05-13 07:45:59

回答 2查看 669关注 0票数 1

我有下面的代码，为了让它在每个主要版本的结果中只显示一行，我需要合并所有类似的行。

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
,swc.MajorVersion
,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid


WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
GROUP BY
arp.DisplayName, arp.Publisher, swc.MajorVersion

order by
arp.DisplayName asc

结果：

Count   displayname MajorVersion

41  Adobe Reader 8  8

1   Adobe Reader 8.1.1  8

40  Adobe Reader 8.1.3  8

1   Adobe Reader 9.3    9

1   Adobe Reader 9.3.3  9

1   Adobe Reader 9.4.0  9

1   Adobe Reader 9.5.5  9

4   Adobe Reader X (10.1.13)    10

1   Adobe Reader X (10.1.4) 10

1   Adobe Reader X MUI  10

7   Adobe Reader XI 11

139 Adobe Reader XI (11.0.02)   11

1   Adobe Reader XI (11.0.03)   11

2   Adobe Reader XI (11.0.06)   11

28  Adobe Reader XI (11.0.07)   11

1   Adobe Reader XI (11.0.08)   11

62  Adobe Reader XI (11.0.09)   11

5527    Adobe Reader XI (11.0.10)   11

3   Adobe Reader XI (11.0.10)  MUI  11

sql-server

回答 2

Database Administration用户

发布于 2015-05-13 08:00:28

尝试如下:我删除了几个字段，并将组更改为

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
-- remove these fields
--,swc.MajorVersion
--,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid


WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
--change the group by
--GROUP BY
--arp.DisplayName, arp.Publisher, swc.MajorVersion

-- to this
group by arp.DisplayName


order by
arp.DisplayName asc

票数 1

Database Administration用户

发布于 2015-05-13 08:56:31

我没有办法测试这一点，但我使用了显示名称的支点和为其他产品创建的动态列，并将清理后的Inv_AddRemoveProgram表移到临时表中：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SELECT *
INTO #tmp
FROM Inv_AddRemoveProgram arp
WHERE arp.DisplayName NOT IN
        (
        'Power Scheme Plug-in Setup'
        ,'Altiris Inventory Agent'
        ,'Patch Management Agent'
        ,'Deployment Solution Agent'
        ,'Software Management Solution Agent'
        ,'Altiris Application Metering Agent'
        ,'Symantec pcAnywhere'
        ,'Symantec_pcAnywhere_plugin_installer'
        ,'Software Management Solution Plugin'
        )
    AND arp.DisplayName NOT LIKE 'Security Update%'
    AND arp.DisplayName NOT LIKE 'Update for%'
    AND arp.DisplayName NOT LIKE 'Hotfix for%'
    AND arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(arp.displayname) 
                    from Inv_AddRemoveProgram arp
                    group by arp.displayname
                    order by arp.displayname
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT MajorVersion, ' + @cols + ' 
FROM (SELECT
        arp.displayname
        ,swc.MajorVersion
        ,arp.Publisher

        FROM
        #tmp arp
        INNER JOIN
        vComputer vc
        ON arp.[_ResourceGuid] = vc.[Guid]
        inner join
        [Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid

        order by
        arp.DisplayName asc) AS SOURCE
PIVOT
(
    COUNT(DisplayName)
    FOR DisplayName IN (' + @cols + ')
) AS PIVOT;'

EXECUTE @query;