nba

来源：互联网发布：php中while的用法编辑：程序博客网时间：2024/04/30 22:56

求取连续获得总冠军的球队的的球队名称，连续夺冠的开始时间，连续夺冠的结束时间

create table nba(
team varchar2(10),
y number(10) primary key
);

delete from nba;
insert into nba values('活塞',1990);
insert into nba values('公牛',1991);
insert into nba values('公牛',1992);
insert into nba values('公牛',1993);
insert into nba values('火箭',1994);
insert into nba values('火箭',1995);
insert into nba values('公牛',1996);
insert into nba values('公牛',1997);
insert into nba values('公牛',1998);
insert into nba values('马刺',1999);
insert into nba values('湖人',2000);
insert into nba values('湖人',2001);
insert into nba values('湖人',2002);
insert into nba values('马刺',2003);
insert into nba values('活塞',2004);
insert into nba values('马刺',2005);
insert into nba values('热火',2006);
insert into nba values('马刺',2007);
insert into nba values('凯尔',2008);
insert into nba values('湖人',2009);
insert into nba values('湖人',2010);
commit;

SELECT T1.TEAM
,MIN(T1.Y)
,MAX(T1.Y)
FROM
(
SELECT TEAM
,Y
,Y-ROW_NUMBER() OVER (PARTITION BY TEAM ORDER BY Y) ROW_NUM
FROM NBA
)T1
HAVING MIN(T1.Y)!=MAX(T1.Y)
GROUP BY T1.TEAM,T1.ROW_NUM
ORDER BY MIN(T1.Y)

阅读全文

0 0