请哪位高手指点以下,我有个数据库表,想过滤重复数据,表结构,内容如下
ID USER_ID SCORE
1 1 80
2 1 88
3 2 78
4 2 89
我希望通过一条SQL语句得到如下结果
ID USER_ID SCORE
2 1 88
4 2 89就是过滤掉USER_ID重复的数据,并找出同一USER_ID中SCORE的最大的那些记录
ID USER_ID SCORE
1 1 80
2 1 88
3 2 78
4 2 89
我希望通过一条SQL语句得到如下结果
ID USER_ID SCORE
2 1 88
4 2 89就是过滤掉USER_ID重复的数据,并找出同一USER_ID中SCORE的最大的那些记录
(select tb.*,
row_number() over(partition by USER_ID order by SCORE desc nulls last) rn
from tb
)t
where t.rn=1
;
select * from tb t where score=(select max(score) from tb where t.user_id=user_id);select * from tb t where not exists (select * from tb where t.user_id=user_id and score<t.score);
select ID,USER_ID,SCORE
from (select ID,USER_ID,SCORE,
row_number() over(partition by USER_ID order by SCORE desc) rn
from tb) a
where a.rn=1select * from tb a where a.score in(select max(score) from tb group by USER_ID)select * from tb a where not exists(select 1 from tb where a.USER_ID=USER_ID and a.score <score )
id number,
user_id number,
score number)insert into stu
select 1,1,80 from dual union all
select 2,1,88 from dual union all
select 3,2,78 from dual union all
select 4,2,89 from dual
1:
select * from
(select id,user_id,score,rank() over(partition by user_id order by score desc) rn from stu) st where st.rn =1;2:
select max(id),user_id,max(score) from stu1 group by user_id;
不是太准确,id取值可能不对
with table1 as (
select 1 id,1 user_id,80 score from dual union all
select 2 id,1 user_id,88 score from dual union all
select 3 id,2 user_id,78 score from dual union all
select 4 id,2 user_id,89 score from dual
)
select * from
(
select id,user_id,score,rank() over(partition by user_id order by score desc) rn
from table1
) a where a.rn =1; ID USER_ID SCORE RN
1 2 1 88 1
2 4 2 89 1
以后还请高手多多指教?