sql - Oracle SQL : How to generate a cricket match point table with details such as win count, 丢失计数等？

Question

我在面试时遇到了这个问题。我必须获得球队的得分、获胜次数、失利次数、平局次数。我的查询给了我正确的结果，但我正在寻找一种方法来缩短查询。有什么帮助吗？

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

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','Pakistan');
insert into match_t values('Srilanka','India','Srilanka');
insert into match_t values('Pakistan','Srilanka','Srilanka');
insert into match_t values('Pakistan','India','India');
insert into match_t values('India','Srilanka','India');
insert into match_t values('Pakistan','India',null);
insert into match_t values('Srilanka','Pakistan',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,3 as m_point,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 host_win,0 as guest_win,0 as match_d from abc where host_w > guest_w
 union all
 select host,2 as m_point,0 as host_win,0 as guest_win,0 as match_d from abc where host_w < guest_w
 union all
 select host,1 as m_point,0 as host_win,0 as guest_win, 1 as match_d from abc where host_w = guest_w
 union all
 select guest,1 as m_point,0 as host_win,0 as guest_win, 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

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

Answer 1

我会使用union all来执行此操作，但查询将很简单:

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;

我对其他答案的复杂程度感到有点惊讶。这个想法非常简单。对于比赛中的每支球队，您都需要标志来指示比赛是胜利、失败还是平局。然后，您想要聚合所有团队的这些标志。