都为空时删除: delete 源表 where isnull(name, '') = '' and isnull(enname, '') = '' and isnull(cnname, '') = ''
SELECT DISTINCT NAME, ENNAME, CNNAME INTO #TMP FROM yourTable WHERE NOT (NAME IS NULL AND ENNAME IS NULL AND CNNAME IS NULL)TRUNCATE TABLE yourTableINSERT INTO yourTable SELECT NAME, ENNAME, CNNAME FROM #TMPDROP TABLE #TMP
alter table tl add id int identity(1,1) delete from t1 where id not in (select max(id) from nn group by name,ename,cnname) or ( name=''and ename='' and cnname='' ) alter table t1 drop column id
没有主键可用 select id=identity(int,1,1),* into ##temp from 表1 delete ##temp where id not in(select min(id) from ##temp group by isnull(NAME,0), isnull(ENNAME,0), isnull(CNNAME,0) truncate table 表1 insert 表1 select NAME, ENNAME, CNNAME from ##temp drop table ##temp 有主键可用 delete 表1 where 主键 not in(select 主键 from 表1 group by isnull(NAME,0), isnull(ENNAME,0), isnull(CNNAME,0))
delete 源表 where isnull(name, '') = '' and isnull(enname, '') = '' and isnull(cnname, '') = ''
SELECT DISTINCT NAME, ENNAME, CNNAME
INTO #TMP
FROM yourTable
WHERE NOT (NAME IS NULL AND ENNAME IS NULL AND CNNAME IS NULL)TRUNCATE TABLE yourTableINSERT INTO yourTable
SELECT NAME, ENNAME, CNNAME
FROM #TMPDROP TABLE #TMP
delete from t1 where id not in (select max(id) from nn group by name,ename,cnname) or ( name=''and ename='' and cnname='' )
alter table t1 drop column id
select id=identity(int,1,1),* into ##temp from 表1
delete ##temp where id not in(select min(id) from ##temp group by isnull(NAME,0), isnull(ENNAME,0), isnull(CNNAME,0)
truncate table 表1
insert 表1 select NAME, ENNAME, CNNAME from ##temp
drop table ##temp
有主键可用
delete 表1 where 主键 not in(select 主键 from 表1 group by isnull(NAME,0), isnull(ENNAME,0), isnull(CNNAME,0))