mysql - 如何使子查询的mysql查询将2个结果合并为1个结果

Question

问题:

如何将这个查询结果合并为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 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;

当前结果

下面是当前查询结果

期望的结果

下面是我想要的结果

Answer 1

您似乎想要总结两支球队之间的所有比赛，并计算每支球队获胜的次数，如果是的话:

为了简化事情，创建一个 View :

create view v_matches as
select radiant_team_id as team1_id, radiant_win as team1_win, dire_team_id as team2_id, 1 - radiant_win as team2_win, leagueid, start_time, series_id, series_type
from matches
union 
select dire_team_id as team1_id, 1 - radiant_win as team1_win, radiant_team_id as team2_id, radiant_win as team2_win, leagueid, start_time, series_id, series_type
from matches

然后:

select team1_id, sum(team1_win) as team1_wins, team2_id, sum(team2_win) as team2_wins, series_id, series_type 
from v_matches
where leagueid = 2096 and start_time >= 1415938900 
  and team1_id = 1848158 and team2_id = 15

您可以将团队名称添加到 View 中，或将第二个查询与您的 teams 表连接起来。