解决Subquery returned more than 1 value报错：查询frequency并展示结果求助

问题说明 #

执行SQL语句时触发报错：

Subquery returned more than 1 value

原SQL语句：

SELECT 
    (SELECT desc_category 
     FROM category 
     WHERE code = id_category) AS Category, 
   notes AS 'Description',
   end_date AS 'Due Date',
   (SELECT frequency 
    FROM category_schedule2 
    WHERE notes = description 
    GROUP BY frequency) 
FROM
    category_schedule 

关联表数据 #

category_schedule2表中description = 'sunlife'的查询结果：

目标输出 #

期望生成的结果表：

报错原因 #

原SQL中SELECT列表里的frequency子查询属于标量子查询，语法要求这类子查询必须返回且仅返回1个值。但SUNLIFE对应ANNUAL和SEMI-ANNUAL两个不同的frequency值，子查询返回了多行，违反语法限制导致报错。

解决方案 #

改用JOIN关联查询替代标量子查询，同时对category_schedule2去重，确保关联后生成符合目标格式的结果：

SELECT 
    c.desc_category AS Category,
    cs.notes AS 'Description',
    cs.end_date AS 'Due Date',
    cs2.frequency
FROM 
    category_schedule cs
LEFT JOIN 
    category c ON c.code = cs.id_category
LEFT JOIN 
    (SELECT DISTINCT description, frequency FROM category_schedule2) cs2 
    ON cs.notes = cs2.description
ORDER BY 
    cs.end_date DESC, cs2.frequency;

关键说明 #

1. 用LEFT JOIN关联category表获取分类名称，替代原标量子查询，规避单行返回限制。
2. 对category_schedule2执行DISTINCT去重后再关联，确保每个description对应的不同frequency生成独立行，匹配目标输出结构。
3. 若仅需保留存在对应frequency的记录，可将LEFT JOIN替换为INNER JOIN，根据业务需求调整。

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