就是有这样一个表
ID COLL01 COLL02
1 TEST TEST
2 TEST TEST
3 TEST1 TEST2
4 TEST TEST2
5 TEST2 TEST
写一个删除记录,删除除了ID,其他字段都重复的字段,这个该怎么写呢?执行了这个SQL语句后的结果是
ID COLL01 COLL02
1 TEST TEST
3 TEST1 TEST2
4 TEST TEST2
5 TEST2 TEST
ID COLL01 COLL02
1 TEST TEST
2 TEST TEST
3 TEST1 TEST2
4 TEST TEST2
5 TEST2 TEST
写一个删除记录,删除除了ID,其他字段都重复的字段,这个该怎么写呢?执行了这个SQL语句后的结果是
ID COLL01 COLL02
1 TEST TEST
3 TEST1 TEST2
4 TEST TEST2
5 TEST2 TEST
Select * From 表 A Where Exists(Select ID From 表 Where COLL01 = A.COLL01 And COLL02 = A.COLL01 And ID < A.ID)--刪除
Delete A From 表 A Where Exists(Select ID From 表 Where COLL01 = A.COLL01 And COLL02 = A.COLL01 And ID < A.ID)
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
where a.coll01 = b.coll01 and a.coll02 = b.coll02 and a.ID<>b.ID
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
---------------沒看清題意的,這個不是所有列都完全相同的。不需要借助臨時表的。
--查詢
Select * From 表 A Where Exists(Select ID From 表 Where COLL01 = A.COLL01 And COLL02 = A.COLL02 And ID < A.ID)--刪除
Delete A From 表 A Where Exists(Select ID From 表 Where COLL01 = A.COLL01 And COLL02 = A.COLL02 And ID < A.ID)
--再寫一種方法
--查詢
Select * From 表 A Where ID Not In(Select Min(ID) From 表 Where COLL01 = A.COLL01 And COLL02 = A.COLL02)--刪除
Delete A From 表 A Where ID Not In(Select Min(ID) From 表 Where COLL01 = A.COLL01 And COLL02 = A.COLL02)
不错。 说不准以后也会用到