java计算两列数据差,计算两列中的值匹配，分别计数

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

此查询的工作原理是根据带有查询的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 |