简例:sql查询重复数据及删除并只保留一条

简单示例:

select * from C_USERROLE where userid in (select USERID from C_USERROLE group by userid, roleid having count(*)>1)
and ROLEID in (select ROLEID from C_USERROLE group by userid, roleid having count(*)>1);

select * from C_USERROLE
where (userid,ROLEID) in (select USERID,ROLEID from C_USERROLE group by userid, roleid having count(*)>1)
and rowid not in (select min(rowid) from C_USERROLE group by userid, roleid having count(*)>1 );

delete C_USERROLE
where (userid,ROLEID) in (select USERID,ROLEID from C_USERROLE group by userid, roleid having count(*)>1)
and rowid not in (select min(rowid) from C_USERROLE group by userid, roleid having count(*)>1 );

This entry was posted in Computer, Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s