如何查询计算球队的特定胜利并找到系列的胜利者 [英] How to query counting specific wins of team and find the winner of the series

问题描述

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

Output: desired
filter using league_id, start_time, and radiant_name and dire_name
ex.

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

输出:我尝试过

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

Using Group by SERIES_ID and SUM but the results is different.

示例.查询

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

例如.不想要的结果〜_〜

ex. not desired results ~_~

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

更新(感谢 https://stackoverflow.com/users/3685967/bsting )
此查询为我提供了正确的结果，但是有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 from matches 那里的联赛= 2096和 start_time> = 1415938900和 ((radiant_team_id = 1848158和dire_team_id = 15) 或(radiant_team_id = 15和dire_team_id = 1848158)) )作为温度 按获胜者分组；

Update (Thanks to https://stackoverflow.com/users/3685967/bsting)
This query gives me correct results but theres 1 problem it gives 2 columns. i need it in 1 row
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 matches 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|

推荐答案

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

I will using case, count and group by

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中创建的测试表

更新

更新问题后，我找不到在评论中发布的开始时间，radiant_team_id和dire_team_id.

I can't find start_time, radiant_team_id and dire_team_id as what you posted in the comment after you updated your question.

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

Below answer might not be the one you want since I'm not so clear on your question after you updated it.

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;

这篇关于如何查询计算球队的特定胜利并找到系列的胜利者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！