根据同一张表中的两列，选择单列的总和

Question

我有一张与下面类似的表格。表格中的p1和p2是指向另一个表格中玩家的id。

id score p1 p2 date
-- ----- -- -- ----
1  12    1  2  2011.10.21
2  23    3  4  2011.10.22
3  21    1  3  2011.10.23
4  35    5  1  2011.10.24
5  11    2  3  2011.10.25

我希望能够获取得分最高的玩家id（p1或p2）。我的解决方案类似于select sum(score)，但是由于一个玩家可能会出现在p1或p2列中，因此我无法构建查询语句。

另外，更大的问题是当我想将得分从高到低排序时，我不知道该怎么做。如果我需要对不同的列进行分组，那么如何汇总和排序得分？我希望得到类似于以下输出结果：

pID score times_played
--- ----- ------------
1   68    3
3   55    3
5   35    1
2   23    2
4   23    1

我的数据库设计有缺陷吗？如果有更加智能的方式，我想知道。我应该需要单独的查询，这样我才能在PHP上合并它们吗？

任何帮助将不胜感激。 谢谢。

PS：我无法想到一个好的主题。随意编辑。

- Sinan

---

一个简化的方法是为每个分数插入两个记录，并仅有一个playerid列。 - dkretz

你如何知道得分属于哪个玩家？你只有一个得分列和两个玩家。 - Icarus

@Icarus 他们是一支球队。当他们一起比赛时，他们得到相同的分数。但是一个球员可能会与另一个球员组成另一支球队参加比赛。 - Sinan

那么最高分将属于两个人吗？如果有两行得分相同，你会怎么做？ - Sparky

@Sinan，看起来Sparky和我对你的问题的解释基本相同，所以我已经恢复了我的初始答案，但是如果得分属于2个玩家，那么得分应该被除以2。如果是这种情况，您可以只需将上次查询中内部选择的得分列除以2。 - Icarus

Answer 1

您可以将播放器放在一列中，如下所示：

select id, score, p1 as player, date from yourtable
union all
select id, score, p2 as player, date from yourtable

您现在拥有一个玩家列。 您可以使用此方法获取所有玩家的得分总和。

select sum(score), player from (
    select id, score, p1 as player, date from yourtable
    union all
    select id, score, p2 as player, date from yourtable
) group by player

现在，您说您也想知道玩家玩了多少次，并按降序排序：

select sum(score), player, count(*) as timesPlayed from (
    select id, score, p1 as player, date from yourtable
    union all
    select id, score, p2 as player, date from yourtable
) group by player order by sum(score) desc

- Icarus

---

这个符合我的需求。如果我理解正确，UNION 基本上创建了一个临时表，然后我们在该数据集上进行选择，对吗？ - Sinan

Answer 2

尝试使用以下方法获取最高分的玩家（忽略平局）

select id,p1,p2 
from table t1
join (select max(score) as MaxS) xx on xx.MaxS = t1.Score
limit 1

获取玩家总分，尝试使用以下代码：

select Player as pID,Sum(tot) as Score, count(*) as TimesPlayed
from
(
select p1 as Player,sum(score) as Tot
from table 
group by p1
union all
select p2,sum(score)
from table 
group by p2
) xx
Group by xx.Player
order by Score desc

- Sparky

Answer 3

除了在表上使用 UNION (ALL) 之外，您可以尝试类似于这样的方法：

SELECT
  CASE p.PlayerNumber WHEN 1 THEN t.p1 ELSE t.p2 END AS pID,
  SUM(t.score) AS score,
  COUNT(*) AS times_played
FROM atable t
  CROSS JOIN (SELECT 1 AS PlayerNumber UNION ALL SELECT 2) p
GROUP BY
  pID /* this is probably MySQL-specific; most, if not all, other major
database systems would require repeating the entire pID expression here, i.e.
GROUP BY
  CASE p.PlayerNumber WHEN 1 THEN t.p1 ELSE t.p2 END
*/
ORDER BY
  score DESC,
  times_played DESC  /* this is based on your result set;
                        you might want to omit it or change it to ASC */

---

更新：回答评论中的一个问题：将结果集与user表连接：

SELECT
  `user`.*,  /* you should probably specify
                the necessary columns explicitly */
  totals.score,
  totals.times_played
FROM `user` u
  INNER JOIN (
    SELECT
      CASE p.PlayerNumber WHEN 1 THEN t.p1 ELSE t.p2 END AS pID,
      SUM(t.score) AS score,
      COUNT(*) AS times_played
    FROM atable t
      CROSS JOIN (SELECT 1 AS PlayerNumber UNION ALL SELECT 2) p
    GROUP BY
      pID
  ) totals ON user.id = totals.pID
ORDER BY
  totals.score DESC,
  totals.times_played DESC

- Andriy M

---

这个可以正常工作。然而我不知道哪个更好或者两者之间是否有性能的提升或下降。避免使用UNION(ALL)会有什么好处吗？ - Sinan

好的，我会期望一些收益，因为当你执行 SELECT ... FROM atable GROUP BY ... UNION ALL SELECT ... FROM atable GROUP BY ... 时，除非我漏掉了什么，否则表会被扫描两次。 - Andriy M

有一件事。我如何将pID与用户数据库相关联？如果我添加, user where pID = user.id，它会显示“未知列pID”。 - Sinan

@Sinan：我会将查询结果用作派生表并将其与“user”连接。请查看我的更新。 - Andriy M