Oracle SQL：如何使用win count,lost count等详细信息生成板球匹

我在接受采访时遇到了这个问题.我必须获得积分,赢得数,失去数,匹配团队的抽奖数.我的查询给了我正确的结果,但我正在寻找一种方法来查看查询.有帮助吗？

我在查询中考虑的某些条件是：

1. If a team wins i am allocating 3 as match point and 2 if a team loses
2. If the match is a tie (when winner is null) i am awarding 1 point to each team.

DDL和DML：

create table match_t(team1 varchar(20),team2 varchar(20),Winner varchar(20));

insert into match_t values('India','Pakistan','India');
insert into match_t values('India','Srilanka','India');
insert into match_t values('Srilanka','Pakistan');
insert into match_t values('Srilanka','India','Srilanka');
insert into match_t values('Pakistan','India');
insert into match_t values('Pakistan',null);
insert into match_t values('Srilanka',null);
Commit;

我对这个问题的回答：

with abc as(
select team1 as host,team2 as guest,case when team1=winner
then 1 else 0 end as host_w,case when team2 = winner
then 1 else 0 end as guest_w  
 from match_t),bac as(
 select host,3 as m_point,1 as host_win,0 as guest_win,0 as match_d from abc where host_w > guest_w
 union all
 select guest,0 as host_win,1 as guest_win,0 as match_d from abc where host_w < guest_w
union all
select guest,2 as m_point,0 as match_d from abc where host_w > guest_w
 union all
 select host,0 as match_d from abc where host_w < guest_w
 union all
 select host,1 as m_point,1 as match_d from abc where host_w = guest_w
 union all
 select guest,1 as match_d from abc where host_w = guest_w
 ),cad as(
 select host as team,sum(m_point) as match_p,sum(host_win+guest_win) as win_c,sum(match_d)  as match_d_c 
 from bac group by host),dac as(select sum(lost_c) as lost_c,team from (select count(*) as lost_c,host as team from abc where host_w=0 and guest_w <> 0
 group by host
 union all
 select count(*) as lost_c,guest as team from abc where guest_w=0 and host_w <> 0
 group by guest) group by team)
  select a.team,a.match_p,a.win_c,b.lost_c,a.match_d_c,a.win_c+b.lost_c+a.match_d_c as no_match from cad a,dac b where a.team=b.team

它给了我正确的结果(参见下文).但我正在寻找一种方法,我可以轻松地获得它,而无需编写如此长的代码

解决方法

select team,sum(is_win) as num_wins,sum(is_loss) as num_losses,sum(is_tie) as num_ties
from ((select team1 as team,(case when winner = team1 then 1 else 0 end) as is_win,(case when winner = team2 then 1 else 0 end) as is_loss,(case when winner is null then 1 else 0 end) as is_tie
        from match_t
       ) union all
       (select team2,(case when winner = team2 then 1 else 0 end) as is_win,(case when winner = team1 then 1 else 0 end) as is_loss,(case when winner is null then 1 else 0 end) as is_tie
        from match_t
       )
      ) t
group by team;

我对其他答案的复杂程度感到有些惊讶.这个想法非常简单.对于比赛中的每支球队,你需要标志,表明比赛是胜利,失败还是平局.然后,您想要在所有团队中聚合这些标志.

【免责声明】本站内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。