77.00

4

5

张三

数学

10.00

2

2

3

120.00

1

77.00

1

mike

0.00

120.00

80.00

Oracle

80.00

mike

0.00

张三丰

3

杨过

SCORE

SCORE

SCORE

--已知：两种排名方式(分区和不分区):使用和不使用partition

10.00

1

1

130.00

1

语文

4

c++

语法：

3

1

select s_id,s_name,sum(score) sum_score from t_score

语文

张三丰

167.00

3

1.顺序：asc|desc 名次与业务相关：

Oracle

S_NAME

S_NAME

张三丰

语文

0.00

3

4

3

李四

李四

1

77.00

80.00

杨过

1

S_NAME

2

1

·查询各科前2名(分区排名)

77.00

Oracle

1

李四

77.00

S_ID

Oracle

示例：找求优秀学员：成绩：降序 迟到次数：升序

130.00

select * from t_score

杨过

2

体育

1

80.00

语文

1

JAVA

2.分区字段：根据什么字段进行分区。

4

0.00

张三丰

80.00

mike

0.00

·类似：新闻表,求栏目点击率在前3位的新闻。

77.00

2

张三

1

·查询原始数据：学号,姓名,科目名,成绩

李四

3

李四

mike

select sc.s_id,sc.s_name,sub_name,sc.score,

数学

80.00

5

李四

名次

张三

S_ID

3

3

李四

group by s_id,s_name ) x

李四

SUM_SCORE

语文

Oracle

3

0.00

数学

1

5

rank() over (partition by 分组字段 order by 排序字段 顺序)

体育

S_NAME

1

rank() over (order by 排序字段 顺序)

rank() over (order by sum_score desc) 名次

2

3

2

80.00

group by s_id,s_name

数学

Oracle

张三丰

1

张三丰

3

where sub_name='Oracle'

1

1

select s_id,s_name,sum(score) sum_score from t_score

名次

3

SUB_NAME

3

2

S_ID

4

语文

数学

1

S_NAME

·分区只是将原始数据进行名次排列(记录数不变),

77.00

Oracle

2

5

from t_score sc

SUB_NAME

4

问题：分区与分组有什么区别?

SUB_NAME

张三

　

JAVA

50.00

from t_score sc

JAVA

Oracle

2

90.00

(partition by sub_name order by score desc) 名次

　

名次

1

120.00

207.00

1

77.00

4

select * from ( select sc.s_id,sc.s_name,sub_name,sc.score, dense_rank() over (partition by sub_name order by score desc) 名次 from t_score sc ) x where x.名次<=2

50.00

语文

80.00

2

2

Oracle

from (

Oracle

李四

·查询各同学总分

90.00

2

张三丰

商品表,求各类别销售额在前10位的商品。

5

数学

张三

Oracle

李四

4

SUB_NAME

·根据总分查询各同学名次

3

Oracle

90.00

杨过

SUM_SCORE

80.00

c++

S_ID

1

3

张三丰

select sc.s_id,sc.s_name,sub_name,sc.score,

4

mike

张三

张三丰

名次

c++

杨过

dense_rank() over (order by score desc) 名次

·查询各学生各科排名(分区排名)

Oracle

S_NAME

3

　

4

杨过

SCORE

张三丰

张三丰

S_ID

4

李四

select x.*,

select sc.s_id,sc.s_name,sub_name,sc.score,

207.00

from t_score sc

数学

1

李四

张三丰

语文

3

S_ID

名次

1

数学

2

1

Oracle

SCORE

1

where sub_name='Oracle'

80.00

杨过

·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。

77.00

S_NAME

--两种计算方式(连续,不连续),对应函数：dense_rank,rank

3

杨过

2

2

张三丰

77.00

2

50.00

80.00

SUB_NAME

对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名)

·查询各学生科目为Oracle排名(简单排名)

杨过

张三丰

2

80.00

S_ID

2

167.00

80.00

张三

Oracle

0.00

4

rank() over (order by score desc) 名次

杨过

rank() over

体育

张三丰

李四

3

2