从train数据集向test添加多列时SQL子查询报错求助

问题原因分析 #

你遇到的more than one row returned by a subquery used as an expression报错，核心原因是你写的子查询返回了多行结果——而SQL规则明确要求：作为列表达式的子查询必须只能返回0行或者1行。

为什么会返回多行？看你的train数据集，比如a='v'的记录出现了2次，对应的aml_freq_a都是56。当你用(SELECT aml_freq_a FROM test LEFT JOIN train ON test.a = train.a)这种子查询时，它会把test的每一行和train中所有匹配a值的行做连接，最终返回多个相同的频次值（比如test里某行a='v'，这个子查询会返回2个56），这就违反了子查询作为列的单行要求。

解决方案 #

下面给你两种可行的解决思路，都是确保每个频次值只返回一行：

方案1：先预处理train的频次数据（推荐）

因为train里同一个a/b/c值对应的频次是完全相同的，所以我们可以先把train的频次数据做分组聚合，得到每个字段值对应的唯一频次，再和test做关联：

WITH a_freq AS (
    -- 提取a列每个值对应的唯一频次
    SELECT a, MAX(aml_freq_a) AS aml_freq_a
    FROM train
    GROUP BY a
),
b_freq AS (
    -- 提取b列每个值对应的唯一频次
    SELECT b, MAX(aml_freq_b) AS aml_freq_b
    FROM train
    GROUP BY b
),
c_freq AS (
    -- 提取c列每个值对应的唯一频次
    SELECT c, MAX(aml_freq_c) AS aml_freq_c
    FROM train
    GROUP BY c
)
SELECT 
    test.*,
    a_freq.aml_freq_a,
    b_freq.aml_freq_b,
    c_freq.aml_freq_c
FROM test
LEFT JOIN a_freq ON test.a = a_freq.a
LEFT JOIN b_freq ON test.b = b_freq.b
LEFT JOIN c_freq ON test.c = c_freq.c;

这里用MAX()是因为同一个字段值的频次完全相同，用MIN()/AVG()也能得到一样的结果，目的就是确保每个分组只返回一行。

方案2：在子查询中用聚合函数限制单行

如果你不想提前预处理，可以直接在子查询里用聚合函数，强制让子查询只返回一行结果：

SELECT 
    test.*,
    (SELECT MAX(aml_freq_a) FROM train WHERE train.a = test.a) AS aml_freq_a,
    (SELECT MAX(aml_freq_b) FROM train WHERE train.b = test.b) AS aml_freq_b,
    (SELECT MAX(aml_freq_c) FROM train WHERE train.c = test.c) AS aml_freq_c
FROM test;

这种写法更简洁，原理和方案1一致：利用同一个字段值的频次相同的特点，用聚合函数把多行结果压缩成单行。

总结 #

你之前的错误不是左连接的类型问题，而是子查询的写法导致返回了多行结果。只要确保每个频次相关的查询只返回单行，就能解决这个报错。

内容的提问来源于stack exchange，提问作者Clock Slave