如何查询计算球队的特定胜利并找到系列的胜利者

Question

表名称： series_type

id| type| description 
1 |    0| No series (Any team win 1 will be the winner)
2 |    1| Best of 3 (Any team wins 2 will be the winner else draw)
3 |    2| Best of 5 (Any team wins 3 will be the winner else draw)

表格名称： 匹配项

ID| series_id | series_type | league_id | start_time |radiant_name | dire_name | radiant_win
1 |      8313 |           2 |      2096 |   xxxxxxx1 |          LV |       LGD | true 
2 |      8313 |           2 |      2096 |   xxxxxxx2 |         LGD |        LV | false
3 |      8313 |           2 |      2096 |   xxxxxxx3 |          LV |       LGD | false
4 |      8313 |           2 |      2096 |   xxxxxxx4 |          LV |       LGD | false
5 |      8313 |           2 |      2096 |   xxxxxxx5 |         LGD |        LV | false

输出： 使用League_id，start_time，radiant_name和dire_name ex的 所需
过滤器
。

Team "LV" total series wins 3.
Team "LGD" total series wins 2.
Series winner is LV.

输出： 我已经尝试过

使用SERIES_ID和SUM分组，但结果不同。

前任。 询问

SELECT SUM(IF(radiant_win = 1? 1, 0)) as LV, SUM(IF(radiant_win = 1? 0,1)) as LGD

前任。 不想要的结果〜〜_

Team "LV" wins 1.
Team "LGD" wins 4.

更新感谢
此查询为我提供了正确的结果，但是有1个问题，它给出了2列。 我需要一排
select *, count(winner) as count from (select case radiant_win when 1 then radiant_name else dire_name end as winner, radiant_team_id, dire_team_id, series_id, series_type frommatcheswhere leagueid = 2096 and start_time >= 1415938900 and ((radiant_team_id= 1848158 and dire_team_id= 15) or (radiant_team_id= 15 and dire_team_id= 1848158)) ) as temp group by winner;

查询结果 当前查询

winner| radiant_team_id| dire_team_id| series_id| series_type| count|
   LGD|         1848158|           15|      8313|           2|     2
    LV|         1848158|           15|      8313|           2|     3

查询结果 所需查询

winner|loser|  radiant_name|   dire_name|   series_id| series_type| radiant_count| dire_count|
    LV|  LGD|           LV |         LGD|        8313|           2|             3|          2|

Answer 1

我将使用大小写，计数和分组

select winner, count(winner) from 
   (select case radiant_win 
             when 1 then radiant_name 
             else dire_name 
           end as winner
     FROM test.`match` ) as temp
group by winner;

• test.’match’是我在MySQL中创建的测试表

更新

更新问题后，我找不到start_time，radiant_team_id和dire_team_id作为您在评论中发布的内容。

以下答案可能不是您想要的答案，因为您在更新问题后仍不清楚我的问题。

select *, count(winner) as count 
from (select case radiant_win 
            when 1 then radiant_name 
            else dire_name 
        end as winner, 
        radiant_team_id, 
        dire_team_id,
        series_id,
        series_type
    from test.`matches` 
    where league_id = 2096 and 
          start_time >= 1415938900 and 
         ((radiant_team_id= 1848158 and dire_team_id= 15) 
           or (radiant_team_id= 15 and dire_team_id= 1848158)) 
    ) as temp
group by winner;