这样的sql语句如何写？

Question

Delete A from Table1 A Where Exists(Select * from Table1 Where field1=A.field1 And field2=A.field2 And field3<A.field1)

Answer 1

Delete A from Table1 A Where Exists(Select * from Table1 Where field1=A.field1 And field2=A.field2 And field3<A.field3)

Answer 2

--建立测试环境
Create table Table1
(field1 Int,
 field2 Int,
 field3 Int)
--插入数据
Insert Table1 Values(1,                     1,                    2)
Insert Table1 Values(1,                     1,                    3)
Insert Table1 Values(2,                     2,                    4)
Insert Table1 Values(2,                     2,                    5)
Insert Table1 Values(3,                     3,                    6)
GO
--测试
Select * from Table1
Delete A from Table1 A Where Exists(Select * from Table1 Where field1=A.field1 And field2=A.field2 And field3<A.field3)
Select * from Table1
--删除测试环境
Drop table Table1
--结果
/*
field1 field2 field3
1 1 2
1 1 3
2 2 4
2 2 5
3 3 6field1 field2 field3
1 1 2
2 2 4
3 3 6
*/

Answer 3

谢谢paoluo(一天到晚游泳的鱼) 
可是我现在遇到如下情况时，还是不能建立主键：field1        field2         field3
4             4              （空格）
4             4               Null这种情况该如何删除呢？

Answer 4

我找到一个办法，就是把是空格的删掉，null的保留
delete from table1 where field3=''

Answer 5

Create table table1 
(field1 Int,
 field2 Int,
 field3 varchar(10))
--插入数据
Insert table1 Values(1,                     1,                    2)
Insert table1 Values(1,                     1,                    3)
Insert table1 Values(2,                     2,                    4)
Insert table1 Values(2,                     2,                    5)
Insert table1 Values(3,                     3,                    6)
Insert table1 Values(4,                     4,                 ' ' )
Insert table1 Values(4,                     4,                 null)
GOdelete A
from  table111 A,table111 B
where A.field1=B.field1 and A.field2=B.field2 and isnull(A.field3,1)<isnull(B.field3,1)

Answer 6

Delete A from Table1 A Where Exists(Select * from Table1 Where field1=A.field1 And field2=A.field2 And field3<>A.field1)
修改一下