代码之家 › 专栏 › 技术社区 › Shankar Panda

甲骨文:如何生成一个板球比赛点表的细节,如赢计数,输计数等?

oracle sql

Shankar Panda · 技术社区 · 7 年前

我在一次采访中面对这个问题。我必须为一个队赢得分数、赢数、输数、平局数。我的查询给出了正确的结果,但我正在寻找一种方法来简化查询。有什么帮助吗?

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

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

它给了我正确的结果(见下文)。但是我正在寻找一种不需要编写这么长的代码就能很容易地得到它的方法

4 回复 | 直到 7 年前

Gordon Linoff 7 年前

我会用 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;

其他答案有多复杂,我有点惊讶。这个想法很简单。对于比赛中的每一支球队,你都要用旗来表示比赛是赢、输还是平局。然后,您要在所有团队中聚合这些标志。

Vamsi Prabhala 7 年前

解决这个问题的一种方法是 union all 将团队和相应的结果放入列中,然后使用 group by 获得所有的胜利、损失、平局和总分。

select team
,count(*) as num_matches
,sum(points) as total_points
,sum(case when result='Win' then 1 else 0 end) as num_wins
,sum(case when result='Lose' then 1 else 0 end) as num_losses
,sum(case when result='Tie' then 1 else 0 end) as num_ties
from (select winner as team,3 as points,'Win' as result
      from match_t
      where winner is not null
      union all 
      select case when winner = least(team1,team2) then greatest(team1,team2) else least(team1,team2) end,2,'Lose'
      from match_t
      where winner is not null
      union all
      select team1,1,'Tie'
      from match_t
      where winner is null
      union all
      select team2,1,'Tie'
      from match_t
      where winner is null
     ) t
group by team

D-Shih 7 年前

你可以尝试使用条件加重功能一次 UNION ALL 让代码清除。

Oracle 11g R2架构设置 :

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);

查询1 :

SELECT 'Srilanka' Team,
        SUM(CASE WHEN Winner = 'Srilanka' AND (team1 = 'Srilanka' or team2='Srilanka') then 3 
                 WHEN Winner IS NULL  AND (team1 = 'Srilanka' or team2='Srilanka') THEN 1 
                 WHEN team1 = 'Srilanka' or team2='Srilanka' THEN 2 END
           )MATCH_P,
        COUNT(CASE WHEN  Winner = 'Srilanka'  THEN 1 END) WIN_C,
        COUNT(CASE WHEN  Winner <> 'Srilanka'AND (team1 = 'Srilanka' or team2='Srilanka') THEN 1 END) LOST_C,
        COUNT(CASE WHEN  Winner IS NULL AND (team1 = 'Srilanka' or team2='Srilanka') THEN 1 END) MATCH_D_C,
        COUNT(CASE WHEN  (team1 = 'Srilanka' or team2='Srilanka') THEN 1 END) NO_MATCH
FROM match_t
UNION ALL
SELECT 'Pakistan' Team,
        SUM(CASE WHEN Winner = 'Pakistan' AND (team1 = 'Pakistan' or team2='Pakistan') then 3 
                 WHEN Winner IS NULL  AND (team1 = 'Pakistan' or team2='Pakistan') THEN 1 
                 WHEN team1 = 'Pakistan' or team2='Pakistan' THEN 2 END
           )MATCH_P,
        COUNT(CASE WHEN  Winner = 'Pakistan'  THEN 1 END) WIN_C,
        COUNT(CASE WHEN  Winner <> 'Pakistan'AND (team1 = 'Pakistan' or team2='Pakistan') THEN 1 END) LOST_C,
        COUNT(CASE WHEN  Winner IS NULL AND (team1 = 'Pakistan' or team2='Pakistan') THEN 1 END) MATCH_D_C,
        COUNT(CASE WHEN  (team1 = 'Pakistan' or team2='Pakistan') THEN 1 END) NO_MATCH
FROM match_t
UNION ALL
SELECT 'India' Team,
        SUM(CASE WHEN Winner = 'India' AND (team1 = 'India' or team2='India') then 3 
                 WHEN Winner IS NULL  AND (team1 = 'India' or team2='India') THEN 1 
                 WHEN team1 = 'India' or team2='India' THEN 2 END
           )MATCH_P,
        COUNT(CASE WHEN  Winner = 'India'  THEN 1 END) WIN_C,
        COUNT(CASE WHEN  Winner <> 'India'AND (team1 = 'India' or team2='India') THEN 1 END) LOST_C,
        COUNT(CASE WHEN  Winner IS NULL AND (team1 = 'India' or team2='India') THEN 1 END) MATCH_D_C,
        COUNT(CASE WHEN  (team1 = 'India' or team2='India') THEN 1 END) NO_MATCH
FROM match_t

Results :

|     TEAM | MATCH_P | WIN_C | LOST_C | MATCH_D_C | NO_MATCH |
|----------|---------|-------|--------|-----------|----------|
| Srilanka |      13 |     2 |      3 |         1 |        6 |
| Pakistan |      11 |     1 |      3 |         2 |        6 |
|    India |      15 |     4 |      1 |         1 |        6 |

George Joseph 7 年前

您可以选择使用UNPIVOT来获得想要的结果。

with data
  as (select row_number() over(order by 1) as rnk
            ,t.team1
            ,t.team2
            ,t.winner
            ,case when t.winner is not null then 
                  case when t.team1 <> t.winner then t.team1
                       else t.team2
                  end
             end as loser
            ,case when t.winner is null then t.team1 end tie1
            ,case when t.winner is null then t.team2 end tie2
       from match_t t
       )
  ,folded_data
   as (select *
         from data
       unpivot(val for x in (winner
                            ,loser
                            ,tie1
                            ,tie2
                             )
               )t
      )
 select val
        ,sum(case when x='WINNER' then 3
                  when x='LOSER'  then 2
                  when x in ('TIE1','TIE2') then 1
              end) as tot_points
        ,count(rnk) as match_played   
        ,count(case when x='WINNER' then 1 end) as win_count
        ,count(case when x='LOSER' then 1 end) as loser_count
        ,count(case when x in('TIE1','TIE2') then 1 end) as tie_count
   from folded_data
 group by val

这是一个dbfiddle链接

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=0aabf7d1f19ecd010f53903b56427959