表名称: 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;
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
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;
更新
更新问题后,我找不到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;