Oracle的PARTITION BY子句用法示例

-----------------------------------------------------------------------------------------------------------------
 create table employee (empid int ,deptid int ,salary decimal(10,2))
 delete from employee;
 insert into employee values(1,10,5500.00);
 insert into employee values(2,10,4500.00);
 insert into employee values(3,20,1900.00);
 insert into employee values(4,20,4800.00);
 insert into employee values(5,40,6500.00);
 insert into employee values(6,40,14500.00);
 insert into employee values(7,40,44500.00);
 insert into employee values(8,50,6500.00);
 insert into employee values(9,50,7500.00);
 insert into employee values(10,40,4500.00);
 Select * from employee;
------分组排名次-------连续值分组
SELECT empid, deptid, salary, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee;
-----------------------------------------------------------------------------------------------------------------
Create Table NBA(Team varchar(8), Victory varchar(8));

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');

select * from NBA;
--------------连续得冠的起止时间
SELECT rownum - row_number() over(PARTITION BY team ORDER BY Victory) AS gp,
               team,
               Victory
          FROM (SELECT team, Victory FROM nba ORDER BY Victory)
          
          
--连续值分组：Row_Number()----->分组之后排序 
SELECT Team, MIN(Victory) AS BeginYear, MAX(Victory) AS EndYear
FROM (SELECT rownum - row_number() over(PARTITION BY team ORDER BY Victory) AS gp,
               team,
               Victory
          FROM (SELECT team, Victory FROM nba ORDER BY Victory))
GROUP BY team, gp
ORDER BY 2