在 mysql 中使用 SUM 和多个连接

2022-01-09 00:00:00 join sum database mysql relational-database

我一直在寻找解决此问题的方法，有很多类似的问题，但没有任何合适的答案可以帮助我解决问题.

I've been looking for a solution to this, there's plenty of similar questions but none have any proper answers that helped me solve the problem.

首先，我的问题/问题:

First up, my questions/problem:

  1. 我想对多重连接查询中的某些列进行求和和计数
  2. 不能有多个连接吗?我必须嵌套 SELECT 查询吗?

这是我的数据库的 SQL 转储以及示例数据:http://pastie.org/private/vq7qkfer5mwyraudb5dh0a

Here's a SQL dump of my database with sample data: http://pastie.org/private/vq7qkfer5mwyraudb5dh0a

这是我认为可以解决问题的查询:

This is the query I thought would do the trick:

SELECT firstname, lastname, sum(goal.goal), sum(assist.assist), sum(gw.gw), sum(win.win), count(played.idplayer) FROM player
LEFT JOIN goal USING (idplayer)
LEFT JOIN assist USING (idplayer)
LEFT JOIN gw USING (idplayer)
LEFT JOIN win USING (idplayer)
LEFT JOIN played USING (idplayer)
GROUP BY idplayer

我希望生成一个表格，其中目标、助攻、gw、win 和 play 的列是该列中每一行的总和/计数，如下所示:(使用提供的示例数据)

What I'd like this to produce is a table where the columns for goal, assist, gw, win and played are a sum/count of every row in that column, like so: (with supplied sample data)

+-----------+----------+------+--------+----+-----+--------+
| firstname | lastname | goal | assist | gw | win | played |
+-----------+----------+------+--------+----+-----+--------+
| Gandalf   | The White|   10 |      6 |  1 |   1 |      2 |
| Frodo     | Baggins  |   16 |      2 |  1 |   2 |      2 |
| Bilbo     | Baggins  |    7 |      3 |  0 |   0 |      2 |
+-----------+----------+------+--------+----+-----+--------+

那么，再次重复上述问题，是否可以通过一个查询和多个连接来实现?

So, to iterate the above questions again, is this possible with one query and multiple joins?

如果您提供解决方案/查询，请解释一下！我是正确的关系数据库的新手，在这个项目之前我从未使用过联接.如果您在必要时避免使用别名，我也将不胜感激.

If you provide solutions/queries, please explain them! I'm new to proper relational databases and I have never used joins before this project. I'd also appreciate if you avoid aliases unless necessary.

我在没有求和和分组的情况下运行了上面的查询，并且我为我执行 SELECT 的每一列得到了一组行，我怀疑这些行随后会相乘或相加，但我在分组和/或执行 sum(TABLE.COLUMN) 可以解决这个问题的印象.

I have run the above query without sum and grouping and I get a set of rows for each column I do a SELECT on, which I suspect is then multiplied or added together, but I was under the impression that grouping and/or doing sum(TABLE.COLUMN) would solve that.

另一件事是，我认为，执行 SELECT DISTINCT 或任何其他 DISTINCT 操作将不起作用，因为这会遗漏一些(重复")结果.

Another thing is that, I think, doing a SELECT DISTINCT or any other DISTINCT operation won't work since that will leave out some ("duplicate") results.

PS.如果重要的话，我的开发机器是 WAMP，但版本将在 ubuntu/apache/mysql/php 上.

PS. If it matters, my dev machine is a WAMP but release will be on ubuntu/apache/mysql/php.

推荐答案

要了解为什么您没有得到预期的答案，请查看以下查询:

To understand why you're not getting the answers you expect, take a look at this query:

SELECT * FROM player LEFT JOIN goal USING (idplayer)

如您所见，左侧的行与右侧的匹配行重复.每次加入都会重复该过程.这是您查询的原始数据:

As you can see, the rows on the left are duplicated for the matching rows on the right. That procedure is repeated for each join. Here's the raw data for your query:

SELECT * FROM player
LEFT JOIN goal USING (idplayer)
LEFT JOIN assist USING (idplayer)
LEFT JOIN gw USING (idplayer)
LEFT JOIN win USING (idplayer)
LEFT JOIN played USING (idplayer)

然后将这些重复值用于 SUM 计算.需要在连接行之前计算 SUM:

Those repeated values are then used for the SUM calculations. The SUMs need to be calculated before the rows are joined:

SELECT firstname, lastname, goals, assists, gws, wins, games_played
FROM player
INNER JOIN 
  (SELECT idplayer, SUM(goal) AS goals FROM goal GROUP BY idplayer) a
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(assist) AS assists FROM assist GROUP BY idplayer) b
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(gw) AS gws FROM gw GROUP BY idplayer) c
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(win) AS wins FROM win GROUP BY idplayer) d
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, COUNT(*) AS games_played FROM played GROUP BY idplayer) e
  USING (idplayer)

SQLFiddle

相关文章