在表中a1 a2 a3
A w1 3
A w2 2
B x1 5
B x2 1
B x3 4对于同一个相同的a1,a3最小的那条纪录保存,其他的删除,操作后如:a1 a2 a3
A w2 2
B x2 1问代码如何实现,谢!
A w1 3
A w2 2
B x1 5
B x2 1
B x3 4对于同一个相同的a1,a3最小的那条纪录保存,其他的删除,操作后如:a1 a2 a3
A w2 2
B x2 1问代码如何实现,谢!
调试欢乐多
from tb a
where not exists(select * from tb where a1=a.a1 and a3>a.a3)
应该是a3>t.a3 而不是a3<t.a3 啊
GO
INSERT INTO T1
SELECT 'A', 'w1', 3
UNION
SELECT 'A', 'w2', 2
UNION
SELECT 'B', 'x1', 5
UNION
SELECT 'B', 'x2', 1
UNION
SELECT 'B', 'x3', 4
---
DELETE A
FROM T1 A , ( SELECT A1, MIN(A3) A3 FROM T1 GROUP BY A1 ) B
WHERE A.A1 = B.A1 AND A.A3 <> B.A3SELECT *
FROM T1DROP TABLE T1
GO
无语了......--对于同一个相同的a1,a3最小的那条纪录保存,其他的删除,操作后如:
declare @t table(a1 varchar(4),a2 varchar(4),a3 int)
insert into @t values('A','w1',3)
insert into @t values('A','w2',2)
insert into @t values('B','x1',5)
insert into @t values('B','x2',1)
insert into @t values('B','x3',4)delete t from @t t where exists(select 1 from @t where a1=t.a1 and a3<t.a3) select * from @t
/*
a1 a2 a3
---- ---- -----------
A w2 2
B x2 1
*/
go
declare @t table(a1 varchar(4),a2 varchar(4),a3 int)
insert into @t values('A','w1',3)
insert into @t values('A','w2',2)
insert into @t values('B','x1',5)
insert into @t values('B','x2',1)
insert into @t values('B','x3',4)delete a
from @t a
where not exists(select * from @t where a1=a.a1 and a3>a.a3)select * from @t
/*
a1 a2 a3
---- ---- -----------
A w2 2
B x2 1
B x3 4
*/
go