SQL查询报错‘Scalar subquery contains more than one row’，请求修正语句

错误原因分析 #

你碰到的Scalar subquery contains more than one row错误，核心问题在于当某个客户在其最新购买年份有多条购买记录时，你的子查询会返回多个purchase.id值，但你用了=运算符（只能匹配单个值），这就导致了冲突。

解决方案1：用IN替换=（保留所有最新年份的购买记录） #

如果你的需求是返回客户最新年份的所有购买记录ID，只需要把判断条件里的=改成IN即可，这样能匹配子查询返回的多个ID值：

SELECT 
    customer.id AS cus_id,
    customer.name AS cus_name,
    purchase.id AS latest_purchase_id
FROM customer
JOIN purchase ON customer.id = purchase.customer_id
WHERE purchase.id IN (
    SELECT id 
    FROM purchase p1 
    WHERE year = ( 
        SELECT max(year) 
        FROM purchase p2 
        WHERE p1.customer_id = p2.customer_id
    )
);

不过这个方案会让同一个客户出现多行结果（对应同一年的多条购买记录），如果这不符合你的需求，可以看下面的方案。

解决方案2：用窗口函数精准筛选（灵活控制返回结果） #

如果希望每个客户只返回一行结果（比如最新年份里任意一条购买记录，或者按购买ID排序后的最新一条），用窗口函数会更清晰，也更高效：

场景A：返回最新年份的任意一条购买记录

WITH ranked_purchases AS (
    SELECT 
        id,
        customer_id,
        year,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY year DESC) AS rn
    FROM purchase
)
SELECT 
    c.id AS cus_id,
    c.name AS cus_name,
    rp.id AS latest_purchase_id
FROM customer c
JOIN ranked_purchases rp ON c.id = rp.customer_id
WHERE rp.rn = 1;

这里ROW_NUMBER()会给每个客户的购买记录按年份倒序排名，排名1的就是最新年份的记录（同一年的记录会随机分配排名，只取一条）。

场景B：返回最新年份里购买ID最大的那条记录（如果ID有顺序意义）

如果购买ID的字符数字混合格式里，数字部分有递增意义，可以调整窗口函数的排序规则：

WITH ranked_purchases AS (
    SELECT 
        id,
        customer_id,
        year,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY year DESC, id DESC) AS rn
    FROM purchase
)
SELECT 
    c.id AS cus_id,
    c.name AS cus_name,
    rp.id AS latest_purchase_id
FROM customer c
JOIN ranked_purchases rp ON c.id = rp.customer_id
WHERE rp.rn = 1;

场景C：保留最新年份的所有记录（类似方案1，但用窗口函数实现）

如果想保留同一年的所有购买记录，把ROW_NUMBER()换成RANK()即可：

WITH ranked_purchases AS (
    SELECT 
        id,
        customer_id,
        year,
        RANK() OVER (PARTITION BY customer_id ORDER BY year DESC) AS rn
    FROM purchase
)
SELECT 
    c.id AS cus_id,
    c.name AS cus_name,
    rp.id AS latest_purchase_id
FROM customer c
JOIN ranked_purchases rp ON c.id = rp.customer_id
WHERE rp.rn = 1;

RANK()会给同一年的记录分配相同的排名1，这样就能返回所有最新年份的购买记录。

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