SQL连续登录的情况的处理

--有如下一张表NBA，表中的内容是每年获得冠军的球队的名字。

--查询出在此期间连续获得冠军的有哪些，其连续的年份的起止时间是多少，结果如下：

--使用的方法（自关联）

SELECT C.TEAM,MIN(Y1) START_YEAR,MAX(Y2) END_YEAR
FROM (SELECT A.TEAM,A.Y-ROWNUM T_YEAR,A.Y AS Y1,B.Y AS Y2,ROWNUM
            FROM NBA A
              JOIN NBA B
                ON A.TEAM=B.TEAM
            WHERE A.Y=B.Y-1) C
GROUP BY TEAM,T_YEAR
ORDER  BY START_YEAR;

----方法二（使用分析函数）[第二种筛选方式可以用于筛选连续登录的情况]

SELECT TEAM,MIN(Y) AS START_YEAR,MAX(Y) AS END_YEAR
FROM (SELECT N.*,ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) RID,Y-ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) T_YEAR
           FROM NBA N) A
GROUP BY TEAM,T_YEAR
HAVING MIN(Y) != MAX(Y);  --过滤掉没有连续获得冠军的球队

SELECT TEAM,MIN(Y) AS START_YEAR,MAX(Y) AS END_YEAR
FROM (SELECT N.*, Y-ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) T_YEAR
           FROM NBA N) A
GROUP BY TEAM,T_YEAR
HAVING count(*)>1;   --过滤掉没有连续获得冠军的球队

 

--建表语句如下：

CREATE TABLE NBA(
TEAM VARCHAR2(20),
Y NUMBER(4)
);
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;