mssql sqlserver 三种数据表数据去重方法分享

            create
            table test(keyId intidentity,sort varchar(10),
info varchar(20))
go---方法1 truncatetable test ;

insertinto test(sort,info)values(‘A‘,‘maomao365.com‘)--1insertinto test(sort,info)values(‘A‘,‘猫猫小屋‘) --2insertinto test(sort,info)values(‘B‘,‘mssql_blog‘) --3insertinto test(sort,info)values(‘B‘,‘优秀的sql——blog‘) --4insertinto test(sort,info)values(‘B‘,‘maomao365‘) --5insertinto test(sort,info)values(‘C‘,‘sql优化blog‘) --6godeletefrom test where test.keyId = (selectmax(b.keyId) from test b where test.sort=b.sort);
select*from test 
---方法2:truncatetable test ; 
insertinto test(sort,info)values(‘A‘,‘maomao365.com‘)
insertinto test(sort,info)values(‘A‘,‘猫猫小屋‘)
insertinto test(sort,info)values(‘B‘,‘mssql_blog‘)
insertinto test(sort,info)values(‘B‘,‘优秀的sql——blog‘)
insertinto test(sort,info)values(‘B‘,‘maomao365‘)
insertinto test(sort,info)values(‘C‘,‘sql优化blog‘)
godeletefrom test 
where keyid notin(selectmin(keyId) from test groupby sort havingcount(sort)>=1);
select*from test 
---方法3:truncatetable test ; 
insertinto test(sort,info)values(‘A‘,‘maomao365.com‘)
insertinto test(sort,info)values(‘A‘,‘猫猫小屋‘)
insertinto test(sort,info)values(‘B‘,‘mssql_blog‘)
insertinto test(sort,info)values(‘B‘,‘优秀的sql——blog‘)
insertinto test(sort,info)values(‘B‘,‘maomao365‘)
insertinto test(sort,info)values(‘C‘,‘sql优化blog‘)
godelete A2 from (
select row_Number() over(partition by sort orderby keyid) as keyId_e,*from test 
) as A2 where A2.keyId_e >1select*from test 
godroptable test