select * from A t where not exists(select 1 from A where username=t.username and score>t.score)

------解决方案--------------------
SQL code

insert into B表
select t.id,t.username,t.score,t.note
from
(select id,username,score,note,
row_number over(partition by username order by score desc) rn
from A表) t
where t.rn=1