内连接与 m/n 关系表和某一列的总和 [英] inner join with m/n relationship tables and sum of a certain column

问题描述

我有三个表

• 代码
• 用户
• codes_users

codes 包含应该分配给特定 users 的代码，codes_users 保存连接信息.

codes contains codes which should get assigned to a specific users the codes_users holds the connection information.

SELECT users.*, 
       codes.code 
FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id

我设法在这个小提琴中与用户一起显示代码:http://sqlfiddle.com/#!2/acd0cb/1

I've manages to display the code with the user in this fiddle: http://sqlfiddle.com/#!2/acd0cb/1

但是当我想计算下载量时，我只会得到一个结果:

But when I like to count the downloads I get only one result:

SELECT users.*, 
       codes.code, 
       Sum(users.downloads) AS _downloadscount 
FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id 

http://sqlfiddle.com/#!2/acd0cb/2

我也喜欢每个用户的下载次数

and also I like to have the download count of each user

推荐答案

你的查询最终需要一个 group by，告诉 DB 你想要一个 per user

Your query needs a group by in the end, telling DB that you want a count per user

SELECT users.*, 
       codes.code, 
       Sum(users.downloads) AS _downloadscount 
FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id 
GROUP  BY users.id; 

这篇关于内连接与 m/n 关系表和某一列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！