代码之家 › 专栏 › 技术社区 › coolsaint Christian C. Salvadó

我有3个表如何连接它们生成一个表?

mysql

coolsaint Christian C. Salvadó · 技术社区 · 15 年前

表a(作者表)

author_id
author_name

表b(员额表)

post_id
author_id

表c(收入表)

post_id (post id is not unique)
post_earning

我想生成一份由每位作者的收入组成的报告。

author_id
author_name
total_earning (sum of earnings of all the posts by author)

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_earnings
2   Redstar 13.99
7   Redleaf 980.18
10  topnhotnews 80.43
11  zmmishad    39.27
13  rashel  1248.34
14  coolsaint   1.66
16  hotnazmul   9.83
17  rubel   0.14
21  mahfuz1986  1.09
48  ripon   12.96
60  KHK 27.81

2 回复 | 直到 15 年前

Yasen Zhelev 15 年前

您确定表中的数据正确吗。可能有一个作者在table a或post-in-posts表中丢失了。

John Hartsock 15 年前

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

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前