主页 / dba / 问题 / 102966
Accepted
John Biddle
Asked: 2015-06-02 08:21:24 +0800 CST

在同一个表中计算两个不同的列

  • 772

我经营一个台球联赛，并在 Excel 中保存了统计数据。我想通过一个网站实现自动化，而 MySQL 将是我的主要数据分析工具，尽管我对那里的了解只是在漫长旅程的开始。

我有 2 个表（仅显示相关表和相关字段）

users:
--------------------
id            BigInt
display_name  VarChar(250)


match_results:
---------------------
id            BigInt
winner_id     BigInt
loser_id      BigInt

我想生成一个结果集以显示为排名表，如下所示：

NAME          WINS     LOSSES
-------------------------------
User2dn        12         7
User1dn        11         8
User5dn        10         8
etc.

我可以管理 ORDER BY

我有其他查询，我已成功加入这两个表，通过为同一个用户表使用 2 个不同的别名来获取 winner_id 和 loster_id 的显示名称，效果很好，但我在这里完全感到困惑。

我真正需要的比这更复杂，因为我需要从 match_results 中的多个其他列中报告，high_run 的最大值（作为赢家或输家）和奖励积分，无论赢家或输家状态如何，这都是一个总和。我想让这个问题尽可能简单，这样我才能理解它，然后再增加额外的复杂性。希望没关系。

mysql

2 个回答

  1. Best Answer

    约翰，

    如果您有一个用户表和一个匹配表，那么您可能应该在它们两者之间有一个引用表，它具有多对多关系。

    users:
--------------------
id (PK)       Int
display_name  VarChar(250)

matches:
---------------------
matchId (PK)   Int
...
...
  -- Other relevant attributes about the match

results:
---------------------
ResultId (PK)      Int
Name           Varchar(100) 
  -- Win, loss, whatever else

match_results:
---------------------
matchId (PK, FK)  Int
UserId (PK, FK)   Int
ResultId (PK, FK) Int

    对于每场比赛，您应该记录该比赛中的每个参与者及其相关结果。除非您计划拥有超过 20 亿个匹配项或用户，否则我会坚持使用 Int。

    现在您可以跨这些表和 SUM() 或 MAX() 或您喜欢的任何聚合进行连接。

    SELECT u.Name , 
   COUNT( CASE
          WHEN r.Name = 'Win' THEN 1
          END )AS Wins , 
   COUNT( CASE
          WHEN r.Name = 'Loss' THEN 1
          END )AS Losses
FROM match_results AS mr INNER JOIN users AS u ON mr.userId = u.id
                       INNER JOIN matches AS m ON mr.matchId = m.id
                       INNER JOIN results AS r ON mr.resultId = r.id;

    编辑：

    使用现有架构，您可以使用子查询来汇总统计信息

      SELECT u.display_name AS NAME, 
    (SELECT COUNT(1) FROM match_results mr WHERE mr.winner_id = u.id) AS Wins,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.loser_id = u.id) AS Losses
  FROM users u
    • 2

  2. 试试这个

    select u.id, u.display_name, 
(select count(winner_id) from match_results where winner_id=u.id) as w,
(select count(loser_id) from match_results where loser_id=u.id) as l
from users u

    通过上述查询，您当前的问题将得到解决。

    如果您需要检索的列多于您可能需要进行一些更改。

    • 0

相关问题