日期:2014-05-19  浏览次数:20473 次

求删除相同数据保留一条的sql语句
select   smt_coname   from   SMT_yp   as   a   where   (select   count(*)   from   SMT_yp   as   b   where   a.smt_coname   =   b.smt_coname)> 1

我这样查询出了   我的表SMT_yp中     字段smt_coname内容完全相同的所有记录   请问我如何删除   这个字段相同的这些重复数据   并保留一条数据

------解决方案--------------------
select distinct smt_coname into #t from SMT_yp

truncate table SMT_yp

select * into SMT_yp from #t

drop table #t
------解决方案--------------------
select distinct smt_coname into #t from SMT_yp

truncate table SMT_yp

select * into SMT_yp from #t

drop table #t
-----------------------
SMT_yp 表中又不是smt_coname 一個字段
------解决方案--------------------
表里有什么时段啊
------解决方案--------------------
表有没有唯一字段??
------解决方案--------------------
create table t
(name varchar(10),addr varchar(100))

insert into t
select 'zhang ', '北京 ' union all
select 'zhang ', '深圳 ' union all
select 'zhang ', '上海 ' union all
select 'zhou ', '北京 ' union all
select 'zhou ', '南京 '


delete t
from
(
select * from #t a
where exists (select 1 from #t where name=a.name and id <a.id )
)a left join t b on a.name=b.name and a.addr=b.addr


select * from t


name addr
---------- ----------------------------------------------------------------
zhang 北京
zhou 北京

(2 row(s) affected)
------解决方案--------------------

借助了臨時表
select id=identity(int,1,1),* into #t from t