【SQL】Oracle开窗函数ROW_NUMBER() OVER() 和 分析函数PARTITION BY

mysql不适用！！！

一、ROW_NUMBER OVER

1. 语法格式：row_number() over(partition by 分组列 order by 排序列 desc)
2. 注意：row_number() over()函数时候，over()里的分组以及排序的执行晚于 where 、group by、 order by 的执行。
3. 查询案例：

// 表
CREATE TABLE `User` (
  `name` varchar(100) DEFAULT NULL,
  `course` varchar(100) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// 数据
INSERT INTO local_database.`User`
(name, course, score)
VALUES('wangwu', 'English', 60);
INSERT INTO local_database.`User`
(name, course, score)
VALUES('wangwu', 'Chinese', 50);
INSERT INTO local_database.`User`
(name, course, score)
VALUES('wangwu', 'Math', 55);

// 查询 ---  按照course分组
SELECT *, ROW_NUMBER() OVER(PARTITION BY u.course ORDER BY u.score) num FROM `User` u
// 查询结果
Id Name Course Score 排名
1002 张三 数学		 90	 	1
1004 李四 数学		 60		 2
1007 王五 数学 		30 		3
1001 李四 英语 		100		 1
1008 张三 英语 		60 		2
1003 王五 英语		 50		 3
1000 张三 语文		 80		 1
1005 李四 语文 		80		 2
1006 王五 语文 		50 		3

二、PARTITION BY 和 GROUP BY 区别

1. 区别：
   partition by(分析性函数) 在于返回的是分组里的每一条数据，并且可以对分组数据进行排序操作;
   group by(聚合函数)只能返回聚合之后的组的数据统计值的记录。

2. 测试：

// 查询 ---  按照name分组
SELECT *, ROW_NUMBER() OVER(PARTITION BY u.name ORDER BY u.score) num FROM `User` u
// 查询结果
Id Name Course Score num
1004 李四 数学		 60		1
1005 李四 语文 		80		2
1001 李四 英语 		100 	3
1007 王五 数学		 30 		1
1006 王五 语文 		50		 2
1003 王五 英语		 50 		3
1008 张三 英语	 	60 		1
1000 张三 语文		 80 		2
1002 张三 数学 		90 		3


// 查询 ---  按照course分组
SELECT *, ROW_NUMBER() OVER(PARTITION BY u.course ORDER BY u.score) num FROM `User` u
// 查询结果
Id Name Course Score 排名
1002 张三 数学		 90	 	1
1004 李四 数学		 60		 2
1007 王五 数学 		30 		3
1001 李四 英语 		100		 1
1008 张三 英语 		60 		2
1003 王五 英语		 50		 3
1000 张三 语文		 80		 1
1005 李四 语文 		80		 2
1006 王五 语文 		50 		3