Oracle:行转列函数,查询重复记录函数,过滤表中重复记录函数

 

1 行转列函数:

select wm_concat(username) from users

2 查询重复记录函数

select t.* from(select t.*,rownum rn from USERS  t where t.username in (select username from USERS group by username having count(*) > 1))  t

3 过滤表中重复记录函数
有一张User表,姓名字段重复
A

select t.* from(select t.*,rownum rn from USERS  t where t.username not in (select username from USERS group by username having count(*) > 1))tunion allselect t.* from(select t.*,rownum rn from USERS  t where t.username in (select username from USERS group by username having count(*) > 1))  t where t.rn=1

B

Sql代码

select * from (select t. *,row_number() over(partition by username order by username) rn from USERS t) tt where tt.rn=1;