子查询返回了 1 个以上的值.这在子查询时是不允许的

问题描述

我正在尝试使用 SQL 从 SCCM 返回 Windows 更新合规性数据，我已经从内置的 SSRS 报告中提取了查询.

I'm trying to return Windows update compliance data from SCCM using SQL, I've extracted the query from a built in SSRS report.

我没有为每个 @colname(计算机组)运行单独的查询，而是尝试通过将 @colname 声明为表并插入多个值来组合成一个查询.

Instead of running a separate query for each @colname (Group of computers) I'm trying to combine into one query by declaring @colname as a table and inserting multiple values.

SQL 返回的错误是:

The error returned in SQL is:

消息 512，级别 16，状态 1，第 6 行
子查询返回了 1 个以上的值.当子查询跟在 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.

我的代码:

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

任何帮助表示赞赏.谢谢

Any help appreciated.Thanks

推荐答案

子查询 select name from @collname 返回多个值，这就是为什么不能使用 =.

The subquery select name from @collname returns more than one value, this is why you can't use =.

使用 IN 谓词代替 =:

where li.title=@title and coll.name in (select name from @collname)

这篇关于子查询返回了 1 个以上的值.这在子查询时是不允许的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！