计算两列中的值匹配，分别计数-Java 学习之路

我有这样一张 table （称之为结果），比赛得分：

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

更好的是，我想根据赢得的比赛百分比除以比赛总数（胜利和亏损）来降序 .

我不想硬编码任何名称，例如赢家=“鲍勃” .

我知道如何制定一个查询来计算每个单独的列，但我还没有弄清楚如何创建3个不同的计数并按名称进行分组 .

2 回答

我想要做的是计算每个名字成为胜利者的次数，以及每次他们是输家的次数，并根据所玩的游戏次数来降序：

此查询的工作原理是根据带有查询的Winner和Loser列生成唯一的名称列表 .

Query

SELECT
 DISTINCT
  Results.Winner AS name
 FROM 
  Results

UNION 

SELECT
 DISTINCT
  Results.Loser AS name
 FROM 
  Results

LEFT JOIN是Winner和Loser COUNT的唯一名单，以获得胜利和失败 .

Query

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

Results

|  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/#!9/68abe/33

回复于 2024-05-06T00:59:26+08:00

另一种方法：

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

回复于 2024-05-06T00:59:26+08:00

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

2 回答

相关问题