为何SQL子查询单独运行正常，嵌套后返回多行报错？

问题背景 #

使用PROC SQL从两张表取数时遇到报错：单独运行子查询能正常得到每个ID的引用计数，但嵌套到外层SELECT查询时触发ERROR: Subquery evaluated to more than one row错误。需求是通过左连接（B表部分数据不在A表中）获取每个ID的引用计数，添加WHERE子句后仍未解决问题。

原SQL代码 #

SELECT 
    A.AREA
    ,A.NAME
    ,B.ID
    ,(
        SELECT 
            COUNT(B.TYPE) 
        FROM 
            EVAL.CITATIONS AS B
        GROUP BY 
            B.ID
        ) 
        AS COUNT_CITATIONS

FROM 
    EVAL.OCT AS A 

LEFT JOIN EVAL.CITATIONS 
    ON A.DBA = B.NAME

ORDER BY A.NAME ASC

;

尝试添加的WHERE子句 #

WHERE FACID = CDPH_CITATIONS.FACID

错误原因 #

你写的标量子查询没有和外层查询建立关联，它会返回所有ID的计数结果（多行），但外层SELECT的每一行只能接受一个单值，因此触发报错。单独运行子查询时返回多行是正常的，但嵌套作为列的子查询必须是单值（标量）。

修正方案 #

提供两种可行的解决方法：

方法1：关联子查询（保留原查询结构） #

给子查询添加关联条件，让它只为当前行的B.ID返回对应的计数：

SELECT 
    A.AREA
    ,A.NAME
    ,B.ID
    ,(
        SELECT COUNT(TYPE) 
        FROM EVAL.CITATIONS AS B_SUB
        WHERE B_SUB.ID = B.ID  /* 关联外层的B表ID，确保子查询返回单值 */
        ) AS COUNT_CITATIONS
FROM 
    EVAL.OCT AS A 
LEFT JOIN EVAL.CITATIONS AS B
    ON A.DBA = B.NAME
ORDER BY A.NAME ASC;

方法2：预聚合后左连接（更高效，适合大数据量） #

先对CITATIONS表按ID聚合计数，再与A表左连接，避免子查询的性能开销：

/* 第一步：预计算每个ID的引用计数 */
PROC SQL;
CREATE TABLE CITATIONS_COUNT AS
SELECT 
    ID,
    COUNT(TYPE) AS COUNT_CITATIONS
FROM EVAL.CITATIONS
GROUP BY ID;
QUIT;

/* 第二步：左连接获取最终结果 */
PROC SQL;
SELECT 
    A.AREA,
    A.NAME,
    B.ID,
    C.COUNT_CITATIONS
FROM EVAL.OCT AS A
LEFT JOIN EVAL.CITATIONS AS B
    ON A.DBA = B.NAME
LEFT JOIN CITATIONS_COUNT AS C
    ON B.ID = C.ID
ORDER BY A.NAME ASC;
QUIT;

补充说明 #

• 你尝试添加的WHERE子句无效，原因有两点：一是表别名不匹配（原代码中B表别名为B，而非CDPH_CITATIONS），二是未与外层查询关联，无法限制子查询返回单行。
• 两种方案都保留了左连接逻辑，确保B表中不在A表的数据也能被包含（对应A表字段会显示缺失值）。

内容的提问来源于stack exchange，提问作者Melanie Kim