我正在尝试使用 SQL 从 SCCM 返回 Windows 更新合规性数据,我已从内置 SSRS 报告中提取了查询。
我没有为每个 @colname (计算机组)运行单独的查询,而是尝试通过将 @colname 声明为表并插入多个值来合并为一个查询。
SQL返回的错误是:
消息 512,第 16 级,状态 1,第 6 行
子查询返回超过 1 个值。当子查询跟在 =、!=、、>= 后面或子查询用作表达式时,这是不允许的。
My code:
DECLARE @title VARCHAR(500);
DECLARE @colname TABLE (name VARCHAR(100));
SET @title = 'ADR | Workstation Software Updates 2017-12-14 09:01:38';
INSERT INTO @colname
VALUES ('All Alpha Workstations'), ('All Beta Workstations'), ('All Delta Workstations');
SELECT DISTINCT
COUNT(*) [Total Clients], li.title, coll.name,
SUM(CASE WHEN ucs.status = 3 OR ucs.status = 1 THEN 1 ELSE 0 END) AS 'Installed / Not Applicable',
SUM(CASE WHEN ucs.status = 2 THEN 1 ELSE 0 END) AS 'Required',
SUM(CASE WHEN ucs.status = 0 THEN 1 ELSE 0 END) as 'Unknown',
ROUND((CAST(SUM(CASE WHEN ucs.status = 3 OR ucs.status = 1 THEN 1 ELSE 0 END) AS float) / COUNT(*)) * 100, 2) AS 'Success %',
ROUND((CAST(COUNT(CASE WHEN ucs.status NOT IN ('3', '1') THEN '*' END) AS FLOAT) / COUNT(*)) * 100, 2) AS 'Not Success%'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN
v_r_system sys ON ucs.resourceid = sys.resourceid
INNER JOIN
v_FullCollectionMembership fcm ON ucs.resourceid = fcm.resourceid
INNER JOIN
v_collection coll ON coll.collectionid = fcm.collectionid
INNER JOIN
v_AuthListInfo LI ON ucs.ci_id = li.ci_id
WHERE
li.title = @title
AND coll.name = (SELECT name FROM @colname) --and ucs.status=2
GROUP BY
li.title, coll.name
ORDER BY
1
任何帮助表示赞赏。
谢谢
子查询select name from @collname
返回多个值,这就是为什么你不能使用=
.
Use IN
谓词而不是=
:
where li.title=@title and coll.name in (select name from @collname)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)