【SQL查询】分区查询Over

1. Over介绍

  Over为开窗函数。就是把满足条件的数据分成几个区域,每个区域可以通过像现实中的“窗口”来观察统计这些数据。

  over不能单独使用,要和分析函数:rank(), dense_rank(), row_number(),ntile()等一起使用。

2. 示例

  (1)脚本

/* 表结构*/
CREATE TABLE student(
  no int,
  ca varchar(20),
  name varchar(50),
  subject  varchar(50),
  scorce int
);

/* 数据 */
INSERT INTO student VALUES(1, '1班', '张三', '语文', 85);
INSERT INTO student VALUES(2, '1班', '张三', '数学', 90);
INSERT INTO student VALUES(3, '1班', '张三', '英语', 70);
INSERT INTO student VALUES(4, '1班', '李四', '语文', 70);
INSERT INTO student VALUES(5, '1班', '李四', '数学', 99);
INSERT INTO student VALUES(6, '1班', '李四', '英语', 62);
INSERT INTO student VALUES(7, '1班', '王五', '语文', 82);
INSERT INTO student VALUES(8, '1班', '王五', '数学', 74);
INSERT INTO student VALUES(9, '1班', '王五', '英语', 89);
INSERT INTO student VALUES(10, '2班', '刘晓希', '语文', 77);
INSERT INTO student VALUES(11, '2班', '刘晓希', '数学', 99);
INSERT INTO student VALUES(12, '2班', '刘晓希', '英语', 80);
INSERT INTO student VALUES(13, '2班', '朱鹏', '语文', 87);
INSERT INTO student VALUES(14, '2班', '朱鹏', '数学', 86);
INSERT INTO student VALUES(15, '2班', '朱鹏', '英语', 76);
INSERT INTO student VALUES(16, '2班', '欧阳雪', '语文', 91);
INSERT INTO student VALUES(17, '2班', '欧阳雪', '数学', 83);
INSERT INTO student VALUES(18, '2班', '欧阳雪', '英语', 77);
commit;

 

  (2)Rank(排名):

select t.*, rank() over(partition by t.subject order by t.scorce desc) as paiming  from student t;

按照科目进行分区,每个区域按照分数进行排序,并得出排序结果的排名号。rank()是跳跃排序,有两个第1名,接下来就是第3名。

  (2)Dense_Rank(密集排名)

select t.*,dense_rank() over(partition by t.subject order by t.scorce desc) as paiming from student t;

按照科目进行分区,每个区域按照分数进行排序,并得出排序结果的排名号。dense_rank()为连续排序,有两个第1名,接下来就是第2名。

  (3)row_number(行号)

select t.*,row_number() over(partition by t.subject order by t.scorce desc) as num from student t;

按照科目进行分区,每个区域按照分数进行排序,并得出排序结果的序号。

posted @ 2018-09-18 19:07  坏脾气  阅读(3903)  评论(0编辑  收藏  举报