在两列中计算值的匹配项，分别计算

jexiocij 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(403)

我有一张这样的表（称为结果），上面有一个锦标赛的分数：

id | Winner | Loser | Score
---+--------+-------+------
1    Bob      Joe     4
2    Roy      Mary    3
3    Joe      Roy     6
4    Mary     Bob     1
5    Ellen    Roy     9
6    José     Roy     2
7    Joe      Bob     7

我想做的是计算每个名字的赢家和输家的次数，然后按玩游戏的次数降序排列：

Name | Wins | Losses | NumberGames
-----+------+--------+------------
Roy    1     3         4
Bob    1     2         3
Joe    2     1         3
Mary   1     1         2
Ellen  1     0         1
José   1     0         1

更好的是，我想按赢的比赛的百分比除以总比赛数（赢加输）来排序。
我不想硬编码任何名字，例如winner=“bob”。
我知道如何构造一个查询来计算每个单独的列，但我还没有弄清楚如何创建3个不同的计数并按名称进行分组。

mysql mariadb

来源：https://stackoverflow.com/questions/50080628/counting-a-values-matches-in-2-columns-counting-separately

2条答案

按热度按时间

yyhrrdl81#

我想做的是计算每个名字的赢家和输家的次数，然后按玩游戏的次数降序排列：
此查询的工作方式是基于查询中的“赢家”和“输家”列生成唯一的名称列表。
查询

SELECT
 DISTINCT
  Results.Winner AS name
 FROM 
  Results

UNION 

SELECT
 DISTINCT
  Results.Loser AS name
 FROM 
  Results

左图是唯一的名单，上面有赢家和输家的计数，以获得赢家和输家。
查询

SELECT
   Results_Wins_Losses.name
 , Results_Wins_Losses.Wins 
 , Results_Wins_Losses.Losses 
 , (Results_Wins_Losses.Wins + Results_Wins_Losses.Losses) AS NumberGames
FROM ( 

  SELECT 
     unique_result_names.name
   , (
      CASE
         WHEN results_wins.Wins IS NULL
         THEN 0
         ELSE results_wins.Wins
       END
      ) AS Wins 
   , (
      CASE
         WHEN results_losses.Losses IS NULL
         THEN 0
         ELSE results_losses.Losses
       END
      ) AS Losses   
  FROM ( 
    SELECT
     DISTINCT
      Results.Winner AS name
     FROM 
      Results

    UNION 

    SELECT
     DISTINCT
      Results.Loser AS name
     FROM 
      Results 

  ) AS unique_result_names

  LEFT JOIN (
    SELECT
       Results.Winner
     , COUNT(*) AS Wins
    FROM  
     Results
    GROUP BY
     Results.Winner
  ) AS results_wins 
  ON
   unique_result_names.name = results_wins.Winner

  LEFT JOIN (
    SELECT
       Results.Loser
     , COUNT(*) AS Losses
    FROM  
     Results
    GROUP BY
     Results.Loser
  ) AS results_losses
  ON
   unique_result_names.name = results_losses.Loser
) AS Results_Wins_Losses
ORDER BY 
 NumberGames DESC

结果

|  name | Wins | Losses | NumberGames |
|-------|------|--------|-------------|
|   Roy |    1 |      3 |           4 |
|   Bob |    1 |      2 |           3 |
|   Joe |    2 |      1 |           3 |
|  Mary |    1 |      1 |           2 |
| Ellen |    1 |      0 |           1 |
|  José |    1 |      0 |           1 |

请参见演示http://sqlfiddle.com/#!1993年9月68日

赞(0）回复(0）举报 2021-06-21

rjjhvcjd2#

另一种方法：

SELECT  Name,
        SUM(Wins) AS Wins,
        SUM(Losses) AS Losses,
        SUM(Wins) + SUM(Losses) AS NumberGames
    FROM  
      (
        SELECT  winner AS Name, COUNT(*) AS Wins, 0 AS Losses
            FROM  Results
            GROUP BY  winner
        UNION  ALL 
        SELECT  loser AS Name,  0 AS Wins, COUNT(*) AS Losses
            FROM  Results
            GROUP BY  loser 
      )
    ORDER BY 4 DESC

赞(0）回复(0）举报 2021-06-21

我来回答

在两列中计算值的匹配项，分别计算

2条答案

相关问题

热门标签

最新问答