如果要删除重复记录,请参见:--刪除重復列 a.如果有ID字段,就是具有唯一性的字段delect table where id not in (select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用到判斷重复的字段b.,如果是判斷所有字段 select * into #aa from table group by id1,id2,.... delete table table insert into table select * from #aac.如果表中有ID的情況select identity(int,1,1) as id,* into #temp from tabel delect # where id not in ( select max(id) from # group by col1,col2,col3...) delect table inset into table(...) select ..... from #temp col1+','+col2+','...col5 組合主鍵 select * from table where col1+','+col2+','...col5 in (select max(col1+','+col2+','...col5) from table where having count(*)>1 group by col1,col2,col3,col4 ) group by 子句后跟的字段就是你用到判斷重复的字段d. select identity(int,1,1) as id,* into #temp from tabel select * from #temp where id in ( select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)e. alter table yourtable add rownum int identity(1,1) go delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名) go alter table yourtable drop column rownum gof. alter table 表 add newfield int identity(1,1) delete 表 where newfield not in( select min(newfield) from 表 group by 除newfield外的所有字段 )alter table 表 drop column newfield g. -- 刪除表中重復的記錄 DELETE delete1 FROM tabTest delete1 JOIN tabTest delete2 ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id
--表中要有主键才行,因为可能有重复的,满足最优条件的记录--假设id为主键--删除处理 delete 表 from 表 a left join( select id=min(id) from 表 a join( select 字段1,aa=max( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) from 表 group by 字段1 )b on a.字段1=b.字段1 and b.aa=( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) group by b.字段1 )b on a.id=b.id where b.id is null
--测试--测试数据 create table 表(id int identity(1,1),字段1 int,字段2 int,字段3 int,字段4 int) insert 表 select 1,0,0,0 union all select 1,0,0,0 union all select 2,0,0,0 union all select 2,4,3,1 union all select 2,0,3,0 union all select 3,4,2,8 go--删除处理 delete 表 from 表 a left join( select id=min(id) from 表 a join( select 字段1,aa=max( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) from 表 group by 字段1 )b on a.字段1=b.字段1 and b.aa=( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) group by b.字段1 )b on a.id=b.id where b.id is null--显示处理结果 select 字段1,字段2,字段3,字段4 from 表 go--删除测试 drop table 表/*--测试结果 字段1 字段2 字段3 字段4 ----------- ----------- ----------- ----------- 1 0 0 0 2 4 3 1 3 4 2 8(所影响的行数为 3 行) --*/
那就用临时表啊?select id=identity(int,1,1),* into #t from 表 delete #t from #t a left join( select id=min(id) from #t a join( select 字段1,aa=max( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) from #t group by 字段1 )b on a.字段1=b.字段1 and b.aa=( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) group by b.字段1 )b on a.id=b.id where b.id is null--显示处理结果 select * from #t--如果要导回原表 delete 表 insert 表 select 字段1,字段2,字段3,字段4 from #t
--表中要有主键才行,因为可能有重复的,满足最优条件的记录--假设id为主键--删除处理 delete 表 from 表 a left join( select id=min(id) from 表 a join( select 字段1,aa=max( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) from 表 group by 字段1 )b on a.字段1=b.字段1 and b.aa=( case 字段2 when 0 then 0 else 1 end +case 字段3 when 0 then 0 else 1 end +case 字段4 when 0 then 0 else 1 end) group by b.字段1 )b on a.id=b.id where b.id is null
--稍加修改--测试数据 create table 表(id int identity(1,1),字段1 int,字段2 varchar(10),字段3 datetime,字段4 int) insert 表 select 1,'1','2003-1-1',0 union all select 1,'1',null,0 union all select 2,'0','2003-1-1',0 union all select 2,null,null,1 union all select 2,'','2003-1-1',1 union all select 3,'4',null,8 go--删除处理 delete 表 from 表 a left join( select id=min(id) from 表 a join( select 字段1,aa=max( case isnull(字段2,'') when '' then 0 else 1 end +case isnull(字段3,'') when '' then 0 else 1 end +case isnull(字段4,'') when '' then 0 else 1 end) from 表 group by 字段1 )b on a.字段1=b.字段1 and b.aa=( case isnull(字段2,'') when '' then 0 else 1 end +case isnull(字段3,'') when '' then 0 else 1 end +case isnull(字段4,'') when '' then 0 else 1 end) group by b.字段1 )b on a.id=b.id where b.id is null--显示处理结果 select 字段1,字段2,字段3,字段4 from 表 go--删除测试 drop table 表/*--测试结果 字段1 字段2 字段3 字段4 ----------- ----------- ----------- ----------- 1 0 0 0 2 4 3 1 3 4 2 8(所影响的行数为 3 行) --*/
a.如果有ID字段,就是具有唯一性的字段delect table where id not in (select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用到判斷重复的字段b.,如果是判斷所有字段
select * into #aa from table group by id1,id2,....
delete table table
insert into table
select * from #aac.如果表中有ID的情況select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp
col1+','+col2+','...col5 組合主鍵
select * from table where col1+','+col2+','...col5 in (select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用到判斷重复的字段d.
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)e.
alter table yourtable add rownum int identity(1,1)
go
delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名)
go
alter table yourtable drop column rownum
gof.
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in(
select min(newfield) from 表 group by 除newfield外的所有字段
)alter table 表 drop column newfield
g.
-- 刪除表中重復的記錄
DELETE delete1
FROM tabTest delete1
JOIN tabTest delete2
ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id
delete 表 from 表 a left join(
select id=min(id) from 表 a join(
select 字段1,aa=max(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
from 表 group by 字段1
)b on a.字段1=b.字段1 and b.aa=(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
group by b.字段1
)b on a.id=b.id
where b.id is null
create table 表(id int identity(1,1),字段1 int,字段2 int,字段3 int,字段4 int)
insert 表 select 1,0,0,0
union all select 1,0,0,0
union all select 2,0,0,0
union all select 2,4,3,1
union all select 2,0,3,0
union all select 3,4,2,8
go--删除处理
delete 表 from 表 a left join(
select id=min(id) from 表 a join(
select 字段1,aa=max(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
from 表 group by 字段1
)b on a.字段1=b.字段1 and b.aa=(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
group by b.字段1
)b on a.id=b.id
where b.id is null--显示处理结果
select 字段1,字段2,字段3,字段4 from 表
go--删除测试
drop table 表/*--测试结果
字段1 字段2 字段3 字段4
----------- ----------- ----------- -----------
1 0 0 0
2 4 3 1
3 4 2 8(所影响的行数为 3 行)
--*/
delete #t from #t a left join(
select id=min(id) from #t a join(
select 字段1,aa=max(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
from #t group by 字段1
)b on a.字段1=b.字段1 and b.aa=(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
group by b.字段1
)b on a.id=b.id
where b.id is null--显示处理结果
select * from #t--如果要导回原表
delete 表
insert 表 select 字段1,字段2,字段3,字段4 from #t
delete 表 from 表 a left join(
select id=min(id) from 表 a join(
select 字段1,aa=max(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
from 表 group by 字段1
)b on a.字段1=b.字段1 and b.aa=(
case 字段2 when 0 then 0 else 1 end
+case 字段3 when 0 then 0 else 1 end
+case 字段4 when 0 then 0 else 1 end)
group by b.字段1
)b on a.id=b.id
where b.id is null
我还想请教你。:)如果我的字段1-字段4都不是INT型,而是这样:字段1 字段2 字段3 字段4
varchar char int char字段2的值有几类:
null
空字符 //什么也没有
'344543'
我又应该怎么对这个进行判断呢??
create table 表(id int identity(1,1),字段1 int,字段2 varchar(10),字段3 datetime,字段4 int)
insert 表 select 1,'1','2003-1-1',0
union all select 1,'1',null,0
union all select 2,'0','2003-1-1',0
union all select 2,null,null,1
union all select 2,'','2003-1-1',1
union all select 3,'4',null,8
go--删除处理
delete 表 from 表 a left join(
select id=min(id) from 表 a join(
select 字段1,aa=max(
case isnull(字段2,'') when '' then 0 else 1 end
+case isnull(字段3,'') when '' then 0 else 1 end
+case isnull(字段4,'') when '' then 0 else 1 end)
from 表 group by 字段1
)b on a.字段1=b.字段1 and b.aa=(
case isnull(字段2,'') when '' then 0 else 1 end
+case isnull(字段3,'') when '' then 0 else 1 end
+case isnull(字段4,'') when '' then 0 else 1 end)
group by b.字段1
)b on a.id=b.id
where b.id is null--显示处理结果
select 字段1,字段2,字段3,字段4 from 表
go--删除测试
drop table 表/*--测试结果
字段1 字段2 字段3 字段4
----------- ----------- ----------- -----------
1 0 0 0
2 4 3 1
3 4 2 8(所影响的行数为 3 行)
--*/
我执行的时候碰到这样的错误:服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 'Null ' 转换为数据类型为 int 的列时发生语法错误。