Oracle SQL:如何使用win count,lost count等详细信息生成板球匹
发布时间:2020-12-12 12:52:03 所属栏目:百科 来源:网络整理
导读:我在接受采访时遇到了这个问题.我必须获得积分,赢得数,失去数,匹配团队的抽奖数.我的查询给了我正确的结果,但我正在寻找一种方法来查看查询.有帮助吗? 我在查询中考虑的某些条件是: 1. If a team wins i am allocating 3 as match point and 2 if a team l
我在接受采访时遇到了这个问题.我必须获得积分,赢得数,失去数,匹配团队的抽奖数.我的查询给了我正确的结果,但我正在寻找一种方法来查看查询.有帮助吗?
我在查询中考虑的某些条件是: 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 它给了我正确的结果(参见下文).但我正在寻找一种方法,我可以轻松地获得它,而无需编写如此长的代码 解决方法我会使用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; 我对其他答案的复杂程度感到有些惊讶.这个想法非常简单.对于比赛中的每支球队,你需要标志,表明比赛是胜利,失败还是平局.然后,您想要在所有团队中聚合这些标志. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
推荐文章
站长推荐
- actionscript-3 – AS3 – 当它进行闭环时自动填
- Ruby 使用 Berkeley DB 数据库
- 循环解析xml dom
- react native key,ref,bind的作用和使用
- c# – 无法编译monocov(Mac OS x 10.7.4)
- 复制 – PostgreSQL 9:在镜像上的主复制上清空表
- ruby-on-rails – accept_nested_attributes_for
- c# – 修改内部.NET类的方法实现
- 如何配置Flash Media Live Encoder (FMLE)从而使
- flex4 xmlns:local Style:@namespace CDATA、Arr
热点阅读