mysql - 我有 3 个表，如何将它们连接起来生成一个表？

Question

表a(作者表)

author_idauthor_name

Table b (Post Table)

post_idauthor_id

Table c (Earning Table)

post_id (post id is not unique)post_earning

I wanted to generate a report consist of earnings per author.

author_idauthor_nametotal_earning (sum of earnings of all the posts by author)

The SQL Query used:

SELECT
   a.author_id,
   a.author_name,
   sum(post_earnings) as total_earnings
FROM TableA a
Inner Join TableB b on b.author_id = a.author_id
Inner Join TableC c on c.post_id = b.post_id
Group By 
   a.author_id,
   a.author_name

我得到的结果是这样的:

ID  user_login  total_earnings2   Redstar 13.997   Redleaf 980.1810  topnhotnews 80.4311  zmmishad    39.2713  rashel  1248.3414  coolsaint   1.6616  hotnazmul   9.8317  rubel   0.1421  mahfuz1986  1.0948  ripon   12.9660  KHK 27.81

总收入的总和实际上是2863.22。但是，如果我将结果表的所有值相加，我会得到 2415。问题出在哪里？可以从第一个评论中的链接下载使用的示例表。

Answer 1

SELECT
   a.author_id,
   a.author_name,
   sum(post_earnings) as total_earnings
FROM TableA a
Inner Join TableB b on b.author_id = a.author_id
Inner Join TableC c on c.post_id = b.post_id
Group By 
   a.author_id,
   a.author_name