db2的row_number()over() 等于oracle的row_number()over() 还是等于oracl
来源:学生作业帮 编辑:作业帮 分类:综合作业 时间:2024/10/06 17:03:10
db2的row_number()over() 等于oracle的row_number()over() 还是等于oracle的rank()over()
db2的row_number()over() 等于oracle的row_number()over()
也就是顺序排列, 1,2,3,4,5,6,7
rank()over() 是排名不连续 也就是 数据有可能是 : 1,1,3,3,5,6,7 这样的.
下面是一个例子:测试表/数据
CREATE TABLE test_course (
student_name VARCHAR(10), -- 学生
course_name VARCHAR(10), -- 课程
grade INT -- 成绩
);
INSERT INTO test_course VALUES('甲', '语文', 95);
INSERT INTO test_course VALUES('乙', '语文', 85);
INSERT INTO test_course VALUES('丙', '语文', 75);
INSERT INTO test_course VALUES('丁', '语文', 65);
INSERT INTO test_course VALUES('戊', '语文', 55);
INSERT INTO test_course VALUES('己', '语文', 50);
INSERT INTO test_course VALUES('庚', '语文', 60);
INSERT INTO test_course VALUES('辛', '语文', 70);
INSERT INTO test_course VALUES('壬', '语文', 80);
INSERT INTO test_course VALUES('奎', '语文', 90);
INSERT INTO test_course VALUES('甲', '数学', 90);
INSERT INTO test_course VALUES('乙', '数学', 80);
INSERT INTO test_course VALUES('丙', '数学', 70);
INSERT INTO test_course VALUES('丁', '数学', 60);
INSERT INTO test_course VALUES('戊', '数学', 50);
INSERT INTO test_course VALUES('己', '数学', 50);
INSERT INTO test_course VALUES('庚', '数学', 60);
INSERT INTO test_course VALUES('辛', '数学', 70);
INSERT INTO test_course VALUES('壬', '数学', 85);
INSERT INTO test_course VALUES('奎', '数学', 95);
ROW_NUMBER 顺序编号 SQL> SELECT
2 ROW_NUMBER() OVER( ORDER BY SUM(grade) DESC) AS NO,
3 student_name,
4 SUM(grade) AS AllGrade
5 FROM
6 test_course
7 GROUP BY
8 student_name
9 ORDER BY
10 SUM(grade) DESC
11 ;
NO STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
2 甲 185
3 乙 165
4 壬 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100
已选择10行.
RANK 排名不连续 SELECT
RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,
student_name,
SUM(grade) AS AllGrade
FROM
test_course
GROUP BY
student_name
ORDER BY
SUM(grade) DESC
NO STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
1 甲 185
3 乙 165
3 壬 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100
已选择10行.
还有一个 DENSE_RANK 排名连续 SELECT
DENSE_RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,
student_name,
SUM(grade) AS AllGrade
FROM
test_course
GROUP BY
student_name
ORDER BY
SUM(grade) DESC
NO STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
1 甲 185
2 乙 165
2 壬 165
3 丙 145
4 辛 140
5 丁 125
6 庚 120
7 戊 105
8 己 100
已选择10行.
也就是顺序排列, 1,2,3,4,5,6,7
rank()over() 是排名不连续 也就是 数据有可能是 : 1,1,3,3,5,6,7 这样的.
下面是一个例子:测试表/数据
CREATE TABLE test_course (
student_name VARCHAR(10), -- 学生
course_name VARCHAR(10), -- 课程
grade INT -- 成绩
);
INSERT INTO test_course VALUES('甲', '语文', 95);
INSERT INTO test_course VALUES('乙', '语文', 85);
INSERT INTO test_course VALUES('丙', '语文', 75);
INSERT INTO test_course VALUES('丁', '语文', 65);
INSERT INTO test_course VALUES('戊', '语文', 55);
INSERT INTO test_course VALUES('己', '语文', 50);
INSERT INTO test_course VALUES('庚', '语文', 60);
INSERT INTO test_course VALUES('辛', '语文', 70);
INSERT INTO test_course VALUES('壬', '语文', 80);
INSERT INTO test_course VALUES('奎', '语文', 90);
INSERT INTO test_course VALUES('甲', '数学', 90);
INSERT INTO test_course VALUES('乙', '数学', 80);
INSERT INTO test_course VALUES('丙', '数学', 70);
INSERT INTO test_course VALUES('丁', '数学', 60);
INSERT INTO test_course VALUES('戊', '数学', 50);
INSERT INTO test_course VALUES('己', '数学', 50);
INSERT INTO test_course VALUES('庚', '数学', 60);
INSERT INTO test_course VALUES('辛', '数学', 70);
INSERT INTO test_course VALUES('壬', '数学', 85);
INSERT INTO test_course VALUES('奎', '数学', 95);
ROW_NUMBER 顺序编号 SQL> SELECT
2 ROW_NUMBER() OVER( ORDER BY SUM(grade) DESC) AS NO,
3 student_name,
4 SUM(grade) AS AllGrade
5 FROM
6 test_course
7 GROUP BY
8 student_name
9 ORDER BY
10 SUM(grade) DESC
11 ;
NO STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
2 甲 185
3 乙 165
4 壬 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100
已选择10行.
RANK 排名不连续 SELECT
RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,
student_name,
SUM(grade) AS AllGrade
FROM
test_course
GROUP BY
student_name
ORDER BY
SUM(grade) DESC
NO STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
1 甲 185
3 乙 165
3 壬 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100
已选择10行.
还有一个 DENSE_RANK 排名连续 SELECT
DENSE_RANK() OVER( ORDER BY SUM(grade) DESC) AS NO,
student_name,
SUM(grade) AS AllGrade
FROM
test_course
GROUP BY
student_name
ORDER BY
SUM(grade) DESC
NO STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
1 甲 185
2 乙 165
2 壬 165
3 丙 145
4 辛 140
5 丁 125
6 庚 120
7 戊 105
8 己 100
已选择10行.
db2的row_number()over() 等于oracle的row_number()over() 还是等于oracl
这句select row_number() over (order by ProductID asc) as RowIn
oracle中的over函数怎么用的,
over and over again是否等于over and over
over等于什么词组?
什么等于over
go over等于什么、
we should make friends from all over the world里的all over等于
oracle 分析函数rank()over()使用,与group by的区别
谁能解释一下oracle中 lead lag over函数的用法
over专项(over的用法)
over the years 等于什么?