select distinct * into #temp from tb truncate table tb insert into tb select * from #temp drop table #temp--与其他表无关联
distinct 删除的话,再生成一列,来判断
select distinct id from tb
本帖最后由 roy_88 于 2011-03-03 10:22:06 编辑
create table #temp(id int) insert #temp select 1 union all select 2 union all select 2 union all select 3 --SQL --#1. 把排序后的数据插入到临时表 select *, flag = null into # from #temp --#2.如下紧邻2行的id一样,则置删除标志 declare @id int, @flag int select @id = MIN(id)-1, @flag = @id from #tempupdate # set flag = @flag, @flag = case when @id = id then 0 else 1 end, @id = id --#3.删除重复行 delete # where flag = 0 --#4.显示结果 select id from #
select distinct * into #temp from tb
truncate table tb
insert into tb
select * from #temp
drop table #temp--与其他表无关联
insert #temp
select 1 union all select 2 union all select 2 union all select 3
--SQL
--#1. 把排序后的数据插入到临时表
select *, flag = null into # from #temp
--#2.如下紧邻2行的id一样,则置删除标志
declare @id int, @flag int
select @id = MIN(id)-1, @flag = @id from #tempupdate #
set flag = @flag,
@flag = case when @id = id then 0 else 1 end,
@id = id
--#3.删除重复行
delete # where flag = 0
--#4.显示结果
select id from #