赵圆瑗的毕业院校,mide022,物业管理费收取标准
话不多说,请看代码:
if not object_id('tempdb..#t') is null drop table #t go create table #t([id] int,[name] nvarchar(1),[memo] nvarchar(2)) insert #t select 1,n'a',n'a1' union all select 2,n'a',n'a2' union all select 3,n'a',n'a3' union all select 4,n'b',n'b1' union all select 5,n'b',n'b2' go
--i、name相同id最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #t a where exists(select 1 from #t where name=a.name and id<a.id)
方法2:
delete a from #t a left join (select min(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id where b.id is null
方法3:
delete a from #t a where id not in (select min(id) from #t where name=a.name)
方法4(注:id为唯一时可用):
delete a from #t a where id not in(select min(id)from #t group by name)
方法5:
delete a from #t a where (select count(1) from #t where name=a.name and id<a.id)>0
方法6:
delete a from #t a where id<>(select top 1 id from #t where name=a.name order by id)
方法7:
delete a from #t a where id>any(select id from #t where name=a.name) select * from #t
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持移动技术网!
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
SQL Server免费版的安装以及使用SQL Server Management Studio(SSMS)连接数据库的图文方法
SQL Server 2017 Developer的下载、安装、配置及SSMS的下载安装配置(图文教程详解)
网友评论