原始数据:
SQL>select * from a;
NO NAME SCOTT
-- ---------- ----------
1 LIFEN 86
2 HONGGE 76
3 AIKAI 90
4 LIFEN 73
5 KUJUHD 69
6 LIFEN 93
7 KOU 80
8 HONGGE 83
......................
select T.*, first_value(scoot) over (partition by name order by scott) F,last_value(scoot) over (partition by name order by scott) L from a T order by no ;
NO NAME SCOTT F L
-- ---------- ---------- ----- -----
1 LIFEN 86 73 86
2 HONGGE 76 76 76
3 AIKAI 90 90 90
4 LIFEN 73 73 73
5 KUJUHD 69 69 69
6 LIFEN 93 73 93
7 KOU 80 80 80
8 HONGGE 83 76 83
可以看出 能得到姓名相同中的最小分数 却不能得到最大分数 也就是last_value這函數里存在order和partition會得不到預期的結果﹐
可以將order by 放在最后面
找出最高分数 (如果有重复姓名)和最低分数的NO
SQL>SELECT T.*,
2 first_value(scott) OVER (PARTITION BY NAME) lowest_scott,
3 last_value(scott) OVER (PARTITION BY NAME) highest_scott
4 FROM a T
5 ORDER BY scott;
第二种方法 就是
select T.*, first_value(scoot) over (partition by name order by scott) F,
last_value(scoot) over (partition by name order by scott rows between unbounded preceding and unbounded following) L from a T order by no ;
ps:分析函数分析的时候是在一个分析窗口中进行的,默认的窗口是当前组的第一行到当前行,如果让窗口是整个组,需要指定窗口子句
删除姓名重复的 分数最高的 保留下来
SQL>delete from a where no not in (select first_value(no) over (partition by name order by scott desc ) no from a );
SQL>select * from a;
NO NAME SCOTT
-- ---------- ----------
1 LIFEN 86
2 HONGGE 76
3 AIKAI 90
4 LIFEN 73
5 KUJUHD 69
6 LIFEN 93
7 KOU 80
8 HONGGE 83
......................
select T.*, first_value(scoot) over (partition by name order by scott) F,last_value(scoot) over (partition by name order by scott) L from a T order by no ;
NO NAME SCOTT F L
-- ---------- ---------- ----- -----
1 LIFEN 86 73 86
2 HONGGE 76 76 76
3 AIKAI 90 90 90
4 LIFEN 73 73 73
5 KUJUHD 69 69 69
6 LIFEN 93 73 93
7 KOU 80 80 80
8 HONGGE 83 76 83
可以看出 能得到姓名相同中的最小分数 却不能得到最大分数 也就是last_value這函數里存在order和partition會得不到預期的結果﹐
可以將order by 放在最后面
找出最高分数 (如果有重复姓名)和最低分数的NO
SQL>SELECT T.*,
2 first_value(scott) OVER (PARTITION BY NAME) lowest_scott,
3 last_value(scott) OVER (PARTITION BY NAME) highest_scott
4 FROM a T
5 ORDER BY scott;
第二种方法 就是
select T.*, first_value(scoot) over (partition by name order by scott) F,
last_value(scoot) over (partition by name order by scott rows between unbounded preceding and unbounded following) L from a T order by no ;
ps:分析函数分析的时候是在一个分析窗口中进行的,默认的窗口是当前组的第一行到当前行,如果让窗口是整个组,需要指定窗口子句
删除姓名重复的 分数最高的 保留下来
SQL>delete from a where no not in (select first_value(no) over (partition by name order by scott desc ) no from a );