在MySQL中将SUM与多个联接一起使用 [英] Using SUM with multiple joins in mysql

问题描述

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

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查询?

1. I want to sum and count certain columns in a multiple join query
2. Is it not possible with multiple joins? Do I have to nest SELECT queries?

这是我的数据库的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，获胜和比赛数列是该列中每一行的总和/计数，如下所示:(带有提供的样本数据)

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

这篇关于在MySQL中将SUM与多个联接一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！