帮助SQL查询连接多个表。

First I will explain the case, I have a table tbl_game with a structure as such. This table contains, the time where the game was started and pair playing the game

首先，我将解释这种情况，我有一个具有类似结构的表tbl_game。这张表包含了游戏开始的时间和配对

|    id    |     time     |     pair_id    |
-----------+--------------+ ---------------
1          | 123123123    |      1         |
2          | 123168877    |      1         |

and I have another table tbl_throws which holds the score for each player. In case you are wondering, this a basic dice rolling game

我还有另一个表tbl_投球，它为每个球员保留得分。如果你想知道，这是一个基本的掷骰子游戏

|   id     |    game_id   |   player_id   |  score  |
-----------+--------------+---------------+---------|
|    1     |    1         |   1           |   2     | 
|    2     |    1         |   2           |   5     |
|    3     |    1         |   1           |   9     |
|    4     |    1         |   2           |   11    |
|    5     |    2         |   1           |   7     | 
|    6     |    2         |   2           |   6     |

Now, id here is the throw id, not the game id. Here each player with player_id 1 and 2 has throws the dice twice and got the respective score as presented all in same game and just one time in another

这里的id是投掷id，而不是游戏id，这里的每个玩家都投了两次骰子，得到了在同一场比赛中出现的分数，而在另一场比赛中只有一次

Now, using these two table, I need to create a record set, that the total score of each player in one game

现在，使用这两个表，我需要创建一个记录集，一个游戏中每个玩家的总分。

|  game_id   | game_time | player1_total | player2_total|
|------------+-----------+---------------+--------------|
|     1      | 123123123 |     11        |     16       |
|     2      | 123168877 |     7         |     6        |

I tried lots of mumbo jumbo queries, but nothing is giving corrent result? What is the correct query for this?

我尝试了很多复杂的查询，但是没有什么能提供相关的结果?对此的正确查询是什么?

Update

Since, most of the answers were bounded by a fact that, player1id and player2id had to be known or fixed.

因为，大多数答案都受到一个事实的限制，即player1id和player2id必须是已知的或固定的。

So may be the information I am about to provide will help to clear the confusion. there is another table, which holds the information of the player. tbl_pupil Structure is like the following

所以，也许我即将提供的信息将有助于消除困惑。还有一个表格，其中包含玩家的信息。tbl_瞳孔结构如下所示

|   id    |   unique_id   | name     |
|---------+---------------+----------|
|   1     |     001       | some     |
|   2     |     002       | another  |

and these player are collectively called, a pair in another table tbl_pair

这些播放器被统称为另一个表tbl_pair中的一对

|   id    |   player1     | player2  |
|---------+---------------+----------|
|   1     |     1         | 2        |

So, now 
select 
    g.id
    g.time
    p1.id as player1id
    p1.name as player1name
    t.score as player1score
    p2.id as player2id
    p2.name as player2name
    t.score as player2score
FROM 
    tbl_game g,
    inner join tbl_pair as pair on g.pair_id = pair.id
    inner join tbl_pupil as p1 on p1.id = pair.player1
    inner join tbl_pupil as p2 on p2.id = pair.player2
    inner join tbl_throw as t on g.id = t.game_id

This is my preliminary query, which brings the record set, on a way as such

这是我的初步查询，在某种程度上带来了记录集

|   id    |   time  |   player1id    |  player1name |   player1score  |   player2id   |    player2name   |  player2score   |
----------------------------------------------------------------------------------------------------------------------------
|   1     |   12    |   1            |  some        |   5             |   2           |    another       |  2              |
|   1     |   12    |   1            |  some        |   5             |   2           |    another       |  5              |
|   1     |   12    |   1            |  some        |   9             |   2           |    another       |  9              |
|   1     |   12    |   1            |  some        |   11            |   2           |    another       |  11             |

Now I am just showing the results of one game id by the way. I don't save sufficient knowledge, to group the above record into one, with player1 separate sum score in one column and playe2's separate sum of score in another column.

现在我只是展示了一个游戏id的结果。我没有保存足够的知识，将上面的记录分组到一个，在一个列中有player1单独的求和值，在另一个列中，playe2的单独的分数相加。

5 个解决方案

#1

Try this:

试试这个:

SELECT 
  tbl_game.id AS game_id, 
  tbl_game.time AS game_time, 
  SUM(CASE WHEN player_id = 1 THEN score ELSE 0 END) AS player1_total, 
  SUM(CASE WHEN player_id = 2 THEN score ELSE 0 END) AS player2_total
FROM tbl_game JOIN tbl_thorws ON tbl_game.id = tbl_throws.game_id
GROUP BY tbl_game.id

#2

This is similar to some of the other answers, but crucially doesn't depend on the player IDs being 1 and 2.

这与其他一些答案类似，但关键的是，这并不取决于玩家id是1和2。

select
    game_id = g.id,
    game_time = g.time,
    player1_total = SUM(case t.player_id when p.player1_id then t.score else 0 end),
    player2_total = SUM(case t.player_id when p.player2_id then t.score else 0 end)
from
    tbl_game g
    join tbl_throws t on g.id = t.game_id
    join ( --Get the player IDs for this game
        select 
            game_id,
            player1_id = MIN(player_id),
            player2_id = MAX(player_id)
        from
            tbl_throws 
        group by game_id
    ) p     
        on p.game_id = t.game_id
group by 
    g.id, g.time

---

Just for fun I've generalized the above out to allow more > 2 players:

为了好玩，我总结了上面的内容，以允许更多> 2玩家:

The 2 CTE tables just show the test data I'm using

这两个CTE表仅仅显示了我正在使用的测试数据

;WITH tbl_game as (
    select ID = 1, time = 123123123, pair_id = 1
    union select ID = 2, time = 123168877, pair_id = 1
),
tbl_throws as (

select id = 1, game_id = 1, player_id = 1, score = 2
union select id = 2, game_id = 1, player_id = 2, score = 5
union select id = 2, game_id = 1, player_id = 3, score = 5
union select id = 3, game_id = 1, player_id = 1, score = 9
union select id = 4, game_id = 1, player_id = 2, score = 11
union select id = 5, game_id = 2, player_id = 1, score = 7
union select id = 6, game_id = 2, player_id = 2, score = 6
)
select
    game_id = g.id,
    game_time = g.time,
    player1_id = MAX(case x.player_no when 1 then t.player_id else 0 end),
    player1_total = SUM(case x.player_no when 1 then t.score else 0 end),
    player1_id = MAX(case x.player_no when 2 then t.player_id else 0 end),
    player2_total = SUM(case x.player_no when 2 then t.score else 0 end),
    player3_id = MAX(case x.player_no when 3 then t.player_id else 0 end),
    player3_total = SUM(case x.player_no when 3 then t.score else 0 end),
    player4_id = MAX(case x.player_no when 4 then t.player_id else 0 end),
    player4_total = SUM(case x.player_no when 4 then t.score else 0 end)
    /* Add more rows for the number of players permitted in a single game */
from
    tbl_game g
    join tbl_throws t on g.id = t.game_id
    cross apply (
        select player_no = COUNT(distinct player_id) 
        from tbl_throws sub 
        where sub.player_id <= t.player_id 
                and Sub.game_id = t.game_id
    ) x
group by 
    g.id, g.time

#3

You need to inner join the two tables, and aggregate your scores. To do the basic pivot you are after I used a CASE statement to aggregate by player.

您需要在内部加入这两个表，并汇总您的分数。在我使用CASE语句对玩家进行集合之后，要做基本的透视。

SELECT  G.Id as Game_Id, 
        G.time as Game_Time,
        SUM(CASE WHEN t.Player_id = 1 THEN t.score ELSE 0 END) as Player1_total,
        SUM(CASE WHEN t.Player_id = 2 THEN t.score ELSE 0 END) as Player2_total
FROM tbl_game G
INNER JOIN tbl_throws T
    ON g.id = t.game_id
GROUP BY g.ID, g.time

#4

I think this should do pretty much what you want to do.

我认为这应该是你想要的。

SELECT 
  tbl_game.id as game_id, 
  tbl_game.time as game_time, 
  SUM(player1.score) as player1_total, 
  SUM(player2.score) as player2_total
FROM tbl_game
INNER JOIN tbl_throws player1 ON player1.game_id = tbl_game.id AND player1.player_id = 1
INNER JOIN tbl_throws player2 ON player2.game_id = tbl_game.id AND player2.player_id = 2
GROUP BY tbl_game.id, tbl_game.time

#5

SELECT t.game_id
     , t.game_time
     , ( SELECT SUM(t.score)
         FROM tbl_throws AS t
         WHERE t.game_id = g.id
           AND player_id = 1
       ) AS player1_total
     , ( SELECT SUM(t.score)
         FROM tbl_throws AS t
         WHERE t.game_id = g.id
           AND player_id = 2
       ) AS player2_total
FROM tbl_game AS g