【SQL练习】将左边的nba表格中，连续夺冠的球队以及连续范围内的开始年份、结束年份查询出来。

将左边的nba表格中，连续夺冠的球队以及连续范围内的开始年份、结束年份查询出来。

期望结果：

—1.建表

create table NBA
(
team VARCHAR2(200),
y NUMBER(4)
);

—2.插入数据

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;

—3.查询语句

select team, min(y) as b, max(y) as e
  from (select team,
               y,
               y - row_number() over(partition by team order by y) as r
          from nba)
 group by team, r
having count(1) >= 2
 order by b;

这属于连续性问题，可以继续看下面这篇文章

数据库连续性问题SQL解决方案