如何从Mysql中的不同表中获取计数总数

Question

I have three different tables for 3 different activities. The common field is user_id.

Performance:

id | user_id | date  | mark    
1  | 123     |  xx   |  20
2  | 456     |  xx   |  10
3  | 789     |  xx   |  5
4  | 123     |  xx   |  10
5  | 456     |  xx   |  10
6  | 789     |  xx   |  5

Internal Activities:

id | user_id | date  | mark    
1  | 123     |  xx   |  20
2  | 456     |  xx   |  10
3  | 789     |  xx   |  5
4  | 123     |  xx   |  10
5  | 456     |  xx   |  10
6  | 789     |  xx   |  5

Other Activities :

id | user_id | date  | mark    
1  | 123     |  xx   |  20
2  | 456     |  xx   |  10
3  | 789     |  xx   |  5
4  | 123     |  xx   |  10
5  | 456     |  xx   |  10
6  | 789     |  xx   |  5

How to fetch each user marks in all the 3 tables for and sort it in DESC. result should be like

id | user_id |  mark    
1  | 123     |   90
2  | 456     |   60
3  | 789     |   30

Thanks in advance

Answer 1

Try this:

SELECT p.id, p.user_id, SUM(p.mark) FROM Performance p 
LEFT JOIN Internal Activities ia ON p.id=ia.id
LEFT JOIN Other Activities oa ON ia.id=oa.id 
GROUP BY p.user_id;