如何从链接到第三张表的两个不同表中获取SUM [英] how to get SUM from two different tables that are linked to third one

问题描述

好吧，问题是我必须对两个链接的表中的两个和求和

ok the problem is that I have to sum two sums from two tables that are linked

第一个表项:

id | user_id | point | hit
1  | 1       |     4 | yes
2  | 1       |     2 | yes
3  | 1       |    -3 | no
4  | 1       |     4 | mb
5  | 2       |     2 | yes
6  | 2       |    -3 | no
7  | 2       |     4 | mb

第二张表的收入:

id | user_id | earning
1  | 1       |   5      
2  | 1       |   2     
3  | 1       |   3     
4  | 1       |   4     
5  | 2       |   2    
6  | 2       |   3    
7  | 2       |   4  

现在我已经尝试过了:

SELECT 
    p.id, 
    SUM( SUM(p.point) + SUM(e.earning) ) AS bb 
FROM 
    points p LEFT JOIN earnings e ON p.user_id = e.user_id 
WHERE 
    (p.hit = "yes" OR p.hit = "no") 
GROUP BY 
    p.user_id

但是我得到了错误的结果

but I got wrong results

我想要这个:

id | bb
1  | 17
2  | 8

谢谢！

推荐答案

如果要获得每位用户的总收入，则可能应该先确定要为其获得收入的用户.最明智的选择可能是使用users表本身作为基础.

If you want total earnings per user, you should probably start by identifying which users you want earnings for. The most sensible option is probably to use the users table itself as the basis.

SELECT user_id,
       (SELECT SUM(point)
          FROM points
         WHERE user_id = u.user_id
       )
       +
       (SELECT SUM(earning)
          FROM earnings
         WHERE user_id = u.user_id
           AND p.hit IN ('yes', 'no')
       ) AS bb
  FROM users

这种方法的另一个好处是可以报告没有积分和/或收入的用户.

This approach has the added benefit of reporting on users who had no points and/or earnings.

这篇关于如何从链接到第三张表的两个不同表中获取SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！