sql - GROUP BY 语句 (SQL) 的问题

Question

我有一个包含以下信息的桌面游戏:

Id_Game  Id_Player1  Id_Player2  Week
--------------------------------------
1211     Peter       John        2
1215     John        Louis       13
1216     Louis       Peter       17

我想获得每个玩家上周玩过的游戏的列表，以及游戏的数量，应该是这样的:

Id_Player  Week  numberGames
-----------------------------
Peter      17    2
John       13    2
Louis      17    2

但是我得到了这个(彼得周通知):

Id_Player  Week  numberGames
-----------------------------
Peter      2     2
John       13    2
Louis      17    2

我所做的是:

  SELECT Id_Player, 
         MAX(Week) AS Week, 
         COUNT(*) as numberGames 
    FROM ((SELECT Id_Player1 as Id_Player, Week 
             FROM Games) 
           UNION ALL
           (SELECT Id_Player2 as Id_Player, Week 
              FROM Games)) AS g2 
GROUP BY Id_Player;

谁能帮我找出错误吗？

Answer 1

Week 列的数据类型是什么？如果 Week 的数据类型是 varchar，您将得到此行为。