MySQL报错Operand should contain 1 column(s)的解决方案咨询

你的SQL报错核心原因有两个：

• 子查询中使用SELECT *会返回多列数据，但salary只能和单个数值相乘，数据库无法处理多列运算
• 子查询未关联当前行的employee_id，会返回全表的bonus_factor结果，无法匹配到单条员工记录

方案1：直接在SELECT中生成bonus_factor并计算bonus #

这种写法简洁直接，适合快速实现需求：

SELECT 
    employee_id,
    -- 生成bonus_factor列
    CASE 
        WHEN employee_id % 2 != 0 AND name NOT LIKE 'M%' THEN 2 
        ELSE 0 
    END AS bonus_factor,
    -- 用salary乘以bonus_factor得到bonus
    salary * (
        CASE 
            WHEN employee_id % 2 != 0 AND name NOT LIKE 'M%' THEN 2 
            ELSE 0 
        END
    ) AS bonus
FROM Employees;

方案2：用子查询/CTE预计算bonus_factor（适配更多条件） #

如果后续需要给bonus_factor添加更多判断条件，这种写法可以避免重复编写CASE逻辑，更易维护：

-- 子查询写法
SELECT 
    employee_id,
    bonus_factor,
    salary * bonus_factor AS bonus
FROM (
    SELECT 
        employee_id,
        name,
        salary,
        CASE 
            WHEN employee_id % 2 != 0 AND name NOT LIKE 'M%' THEN 2 
            ELSE 0 
            -- 后续可在此添加更多WHEN条件
        END AS bonus_factor
    FROM Employees
) AS emp_with_factor;

如果你的数据库支持CTE（如MySQL 8+、PostgreSQL、SQL Server），也可以用更清晰的CTE写法：

WITH emp_with_factor AS (
    SELECT 
        employee_id,
        name,
        salary,
        CASE 
            WHEN employee_id % 2 != 0 AND name NOT LIKE 'M%' THEN 2 
            ELSE 0 
            -- 新增条件直接加在这里即可
        END AS bonus_factor
    FROM Employees
)
SELECT 
    employee_id,
    bonus_factor,
    salary * bonus_factor AS bonus
FROM emp_with_factor;

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