从两个表中选择值和，并按公共外键分组

Question

我有两张桌子，我想：

​

​

1.-从表“A”中选择每个‘id_user’的计数：

查询：

SELECT COUNT(*), user.user_name as user_name
FROM A
INNER JOIN user
ON A.ID_user = user.ID_USER
GROUP BY ID_user;

结果：

​

​

2.-从表"B"中选择每个'id_user‘的值分数

查询：

SELECT SCORE, user.user_name as user_name
FROM B
INNER JOIN user
ON B.id_user = user.id_user_name
GROUP BY id_user;

结果：

​

​

3.-将这两个值相加，并显示按用户名分组的总分：

我的预期产出将是这样的，包括表A和表B的分数之和：

预期产出：

​

​

Answer 1

这是你想要的吗？

select u.*,
       (select sum(a.score) from a where a.id_user = u.id_user) as a_sum,
       (select sum(b.score) from b where b.id_user = u.id_user) as b_sum
from user u;

如果您想要总分，只需将它们加在一起：

select u.*,
       ( (select COUNT(1)  from a where a.id_user = u.id_user) +
         (select coalesce(sum(b.score), 0) from b where b.id_user = u.id_user)
       ) as ab_sum
from user u;