使用聚合时，子查询返回超过1行

Question

子查询返回超过1行？？

SELECT
    `employee`.`employee_id` AS `employee_id`,
    (
    SELECT
        SUM(company.company_profit_left)
    FROM
        company
    GROUP BY
        company.employee_id,
        company.employee_department
) AS profit_left
FROM
    employee
LEFT JOIN `company` ON `company`.`employee_id` = `employee`.`employee_id`
GROUP BY
    `employee`.`employee_id`
HAVING
    SUM(
        company.company_profit_left
    ) = 0

╔════╦══════════════╦════════════╦════════════╦
║ id ║ user_id      ║ profit left║ department ║
╠════╬══════════════╬════════════╬════════════╬
║  1 ║ 1            ║  100       ║    1       ║ 
║  2 ║ 2            ║   50       ║    1       ║ 
║  3 ║ 1            ║   30       ║    2       ║ 
║  4 ║ 2            ║   20       ║    1       ║
║  5 ║ 2            ║   20       ║    3       ║
╚════╩══════════════╩════════════╩════════════╩

下表是我想要达到的目标

╦══════════════╦══════════════╦
║ user_id      ║ profit left  ║ 
╠══════════════╬══════════════╬
║ 1            ║    100       ║  
║ 1            ║    30        ║  
║ 2            ║     70       ║  
║ 2            ║     20       ║  
╚══════════════╩══════════════╩

这就是为什么我认为我需要使用group by来区分user_id

我已经看过了

子查询返回超过1行-MySQL

子查询返回的行数是否超过1行？

但还是不明白。

通过连接两个不同的表，我得到了下表。

谁能帮我解决这个问题？

Answer 1

编辑：根据表结构和所需的输出，我更新了查询并删除了不需要的where和left join。

我不知道你想干什么。但根据我的理解，我纠正了你的质疑：

SELECT
    employee.employee_id AS employee_id,
    profit_left.employee_department
    profit_left.company_profit
FROM
employee
JOIN (
    SELECT
        company.employee_id,
        company.employee_department,
        SUM(company.company_profit_left) as company_profit
    FROM company
    GROUP BY company.employee_id,company.employee_department
) AS profit_left on employee.employee_id=profit_left.employee_id