求删除相同数据保留一条的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
