概览

我有一堆关于我正在举办的比赛的数据，我想以更好的格式呈现它。

有 4 张 table ；前两个是不言自明的，points 和 extras 表本质上是完全相同的东西，它们只是存储在不同的表中，列名略有不同。

数据

    users
    +----+---------------+------+
    | id |     name      | team |
    +----+---------------+------+
    |  1 | John Doe      |    1 |
    |  2 | Jane Lane     |    1 |
    |  3 | Jack Black    |    4 |
    |  4 | Dan Bam       |    3 |
    |  5 | Pam Jan       |    2 |
    |  6 | Pop Tart      |    2 |
    |  7 | John Q        |    1 |
    |  8 | Hugo Strange  |    3 |
    |  9 | Jimmy Neutron |    2 |
    +----+---------------+------+

    teams
    +----+-----------------+
    | id |      name       |
    +----+-----------------+
    |  1 | Team Fun        |
    |  2 | The Dream Team  |
    |  3 | In It To Win It |
    |  4 | Buddies         |
    +----+-----------------+

    points
    +---------+--------+------------+
    | user_id | points |   event    |
    +---------+--------+------------+
    |       1 | 2      | Basketball |
    |       2 | 4      | Basketball |
    |       5 | 1      | Basketball |
    |       8 | 3      | Basketball |
    |       9 | 5      | Basketball |
    |       2 | 8      | Volleyball |
    |       5 | 5.5    | Volleyball |
    |       6 | 6.5    | Volleyball |
    |       7 | 2      | Volleyball |
    |       8 | 4      | Volleyball |
    |       9 | 9.5    | Volleyball |
    |       1 | 2.5    | Dodgeball  |
    |       3 | 3      | Dodgeball  |
    |       4 | 4      | Dodgeball  |
    |       6 | 9      | Dodgeball  |
    |       7 | 2.5    | Dodgeball  |
    |       9 | 3      | Dodgeball  |
    +---------+--------+------------+

    extras
    +---------+--------+---------------------+
    | user_id | points |     description     |
    +---------+--------+---------------------+
    |       1 | 5      | Great Sportsmanship |
    |       3 | 10     | Team Player         |
    |       8 | 5.5    | Most Improved       |
    +---------+--------+---------------------+

What I'm Trying To Do

I want to write a query to return all the events (and "extras") a specific team participated in, the total points from all members of the team, and the participating members in that event.

Example below uses Team Fun (Team 1):

+---------------------+--------+--------------------+------------+
|        event        | points |      members       | members_id |
+---------------------+--------+--------------------+------------+
| Basketball          |      6 | John Doe,Jane Lane | 1,2        |
| Volleyball          |     10 | Jane Lane,John Q   | 2,7        |
| Dodgeball           |      5 | John Doe,John Q    | 1,7        |
| Great Sportsmanship |      5 | John Doe           | 1          |
+---------------------+--------+--------------------+------------+

如果有人能帮我解决这个问题，我将不胜感激!

SQLFiddle

这是一个具有上述数据模式的 SQLFiddle - http://sqlfiddle.com/#!2/e8f97a

最佳答案

您可以使用 UNION 将附加值和积分放在一起:

SELECT user_id, points, event
FROM points
UNION ALL
SELECT user_id, points, description AS event
FROM extras

然后使用它，您可以使用 SUM 和几个 GROUP_CONCAT 来编译您的信息:

SELECT P.event, SUM(P.points) AS points, 
    GROUP_CONCAT(U.name) AS members, GROUP_CONCAT(U.id) AS members_id
FROM teams T
INNER JOIN users U ON T.id = U.team
INNER JOIN
(
    SELECT user_id, points, event
    FROM points
    UNION ALL
    SELECT user_id, points, description AS event
    FROM extras
) P ON U.id = P.user_id
WHERE T.id = @teamId
GROUP BY P.event

SQL Fiddle Example

关于mysql - 使用 SQL 对团队竞争值求和，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19163988/