表名: 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 进行过滤
例如

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

输出: 我试过了

使用 Group by SERIES_ID 和 SUM 但结果不同。

ex. 查询

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

ex. 不是想要的结果 ~_~

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

更新(感谢https://stackoverflow.com/users/3685967/bsting)
此查询为我提供了正确的结果，但它给出了 2 列的 1 个问题。我需要它排成一排
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 <code>matches</code> where 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|

最佳答案

我将使用大小写、计数和分组依据

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;

关于mysql - 如何查询计算团队的特定胜利并找到系列赛的获胜者，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27054402/