最近看了这么一道题,然后发现有的小伙伴的答案有问题,这里仅附上我个人的写法供大家参考,这也是我第一次写,有不好或者不对的地方欢迎大家指出,先谢过大家啦。
原题目如下:
表nba记录了nba(team VARCHAR2(10),y NUMBER(4))夺冠球队的名称及年份:
TEAM Y
活塞 1990
公牛 1991
公牛 1992
公牛 1993
火箭 1994
火箭 1995
公牛 1996
公牛 1997
公牛 1998
马刺 1999
湖人 2000
湖人 2001
湖人 2002
马刺 2003
活塞 2004
马刺 2005
热火 2006
马刺 2007
凯尔特人 2008
湖人 2009
湖人 2010
请写出一条SQL语句,查询出在此期间连续获得冠军的有哪些,其连续的年份的起止时间是多少,结果如下:
TEAM B E
---- ---- ----
公牛 1991 1993
火箭 1994 1995
公牛 1996 1998
湖人 2000 2002
湖人 2009 2010
为了方便大家测试,我附上创表语句和插入数据的语句,如下:
create table NBA
(
team VARCHAR2(20),
y NUMBER(4)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
insert into NBA (team, y)
values ('活塞', 1990);
insert into NBA (team, y)
values ('公牛', 1991);
insert into NBA (team, y)
values ('公牛', 1992);
insert into NBA (team, y)
values ('公牛', 1993);
insert into NBA (team, y)
values ('火箭', 1994);
insert into NBA (team, y)
values ('火箭', 1995);
insert into NBA (team, y)
values ('公牛', 1996);
insert into NBA (team, y)
values ('公牛', 1997);
insert into NBA (team, y)
values ('公牛', 1998);
insert into NBA (team, y)
values ('马刺', 1999);
insert into NBA (team, y)
values ('湖人', 2000);
insert into NBA (team, y)
values ('湖人', 2001);
insert into NBA (team, y)
values ('湖人', 2002);
insert into NBA (team, y)
values ('马刺', 2003);
insert into NBA (team, y)
values ('活塞', 2004);
insert into NBA (team, y)
values ('马刺', 2005);
insert into NBA (team, y)
values ('热火', 2006);
insert into NBA (team, y)
values ('马刺', 2007);
insert into NBA (team, y)
values ('凯尔特人', 2008);
insert into NBA (team, y)
values ('湖人', 2009);
insert into NBA (team, y)
values ('湖人', 2010);
commit;
基于oracle数据库的sql如下:
select max(team),min(y)b ,max(y) e
from (
select team,y from(
select team,
y,
lag(y, 1, null) over(partition by team order by y) before,--上一个
lead(y, 1, null) over(partition by team order by y) after--下一个
from nba) a
where a.y=a.after-1 or a.y=a.before+1
)a
group by y-rownum
order by b
注意的地方和思路如下:
上述划横线的地方,大家可以看做突破口,我用到了lead和lag函数分别以间隔相差1求解出相邻的年份,连续夺冠的含义其实就是说大于1年相邻年份夺冠就是连续夺冠可以2年或者2年以上,所以这里的条件是当前年份和下一年相差1或者当前年份和上一年差1即可,不要用and连接,如果你用and就会漏掉连着2年的,只得到连续3年及以上夺冠的人数。这是第一个横线地方的思路。
第二个横线,其实是利用了等差数列的性质,两个等差数列相减的结果是一个常数,rownum本身就是从1开始递增的等差数列,二y这一列,如果你是连续的,那么连续的这一部分是不是也是一个等差数列,他们的差一定会是一个常数。不同等差数列的差不等,按照他们的差分组就可以啦。这里大家可以上网搜索等差数列通项公式自己减减看,另外,其他求连续的天数啦这样的需求,也可以参考这个思路
以上仅是我的看法,如果那里说的有误或者有偏差,欢迎大家指出,但是不要做键盘侠。