mysql - 有奖金的薪金选择mysql

Closed. This question needs details or clarity。它当前不接受答案。
                            
                        
                    
                
                            
                                
                
                        
                            
                        
                    
                        
                            想改善这个问题吗？添加详细信息并通过editing this post阐明问题。
                        
                        2年前关闭。
                                                                                            
                
        
我有四个桌子
用户，薪水，奖金，罚款
我想为某些用户选择包括所有奖金和罚金在内的薪水
我在每个表中都有user_id（薪水，奖金，罚款）
我怎样才能做到这一点
可以有两个或更多的奖金和罚款
我试着做

BEGIN
   SELECT SUM(salary.salary) + SUM(bonus.bonus) - SUM(penalty.penalty) FROM users
   INNER JOIN salary on salary.user_id = users.id
   INNER JOIN bonus on bonus.user_id = users.id
   INNER JOIN penalty on penalty.user_id = users.id;
   END

但是每次奖金和罚款我都有多个结果
我只想有一个号码
谢谢你的帮助

最佳答案

假设给定的用户在salary，bonus和penalty表中的每个表中都可以有多个记录，这里一种安全的方法是将金额汇总到单独的子查询中，然后将结果合并在一起：

SELECT
    u.id,
    COALESCE(s.salary, 0) + COALESCE(b.bonus, 0) - COALESCE(p.penalty, 0) AS amount
FROM users u
LEFT JOIN
(
    SELECT user_id, SUM(salary) AS salary
    FROM salary
    GROUP BY user_id
) s
    ON u.id = s.user_id
LEFT JOIN
(
    SELECT user_id, SUM(bonus) AS bonus
    FROM bonus
    GROUP BY user_id
) b
    ON u.id = b.user_id
LEFT JOIN
(
    SELECT user_id, SUM(penalty) AS penalty
    FROM penalty
    GROUP BY user_id
) p
    ON u.id = p.user_id

您可以在上面添加WHERE子句以限制一个或多个用户，例如

WHERE u.id = some_value