SET ROWCOUNT 1;WHILE 1=1 BEGIN DELETE tb WHERE groupnum IN(SELECT groupnum FROM tb GROUP BY groupnum HAVING COUNT(*)>1); IF @@ROWCOUNT = 0 BREAK; ENDSET ROWCOUNT 0;
SET ROWCOUNT 1;WHILE 1=1 BEGIN DELETE tb WHERE groupnum IN(SELECT groupnum FROM tb GROUP BY groupnum HAVING COUNT(*)>1); IF @@ROWCOUNT = 0 BREAK; ENDSET ROWCOUNT 0;
SET ROWCOUNT 1;WHILE 1=1 BEGIN DELETE tb WHERE groupnum IN(SELECT groupnum FROM tb GROUP BY groupnum HAVING COUNT(*)>1); IF @@ROWCOUNT = 0 BREAK; ENDSET ROWCOUNT 0;
如果一定要删除,只能这样:create table tb(groupnum int,B int) insert into tb select 1,11 insert into tb select 1,11 insert into tb select 1,10 insert into tb select 2,11 insert into tb select 2,20 insert into tb select 2,20 insert into tb select 2,11 insert into tb select 2,15 insert into tb select 3,23 insert into tb select 3,23 insert into tb select 3,15 insert into tb select 3,6 insert into tb select 3,6 insert into tb select 3,6 go select distinct * into # from tb truncate table tb insert into tb select * from # select * from tb /* groupnum B ----------- ----------- 1 10 1 11 2 11 2 15 2 20 3 6 3 15 3 23(8 行受影响)*/ go drop table tb,#
BEGIN
DELETE tb WHERE groupnum IN(SELECT groupnum FROM tb
GROUP BY groupnum HAVING COUNT(*)>1);
IF @@ROWCOUNT = 0
BREAK;
ENDSET ROWCOUNT 0;
BEGIN
DELETE tb WHERE groupnum IN(SELECT groupnum FROM tb
GROUP BY groupnum HAVING COUNT(*)>1);
IF @@ROWCOUNT = 0
BREAK;
ENDSET ROWCOUNT 0;
BEGIN
DELETE tb WHERE groupnum IN(SELECT groupnum FROM tb
GROUP BY groupnum HAVING COUNT(*)>1);
IF @@ROWCOUNT = 0
BREAK;
ENDSET ROWCOUNT 0;
insert into tb select 1,11
insert into tb select 1,11
insert into tb select 1,10
insert into tb select 2,11
insert into tb select 2,20
insert into tb select 2,20
insert into tb select 2,11
insert into tb select 2,15
insert into tb select 3,23
insert into tb select 3,23
insert into tb select 3,15
insert into tb select 3,6
insert into tb select 3,6
insert into tb select 3,6
go
select distinct * into # from tb
truncate table tb
insert into tb select * from #
select * from tb
/*
groupnum B
----------- -----------
1 10
1 11
2 11
2 15
2 20
3 6
3 15
3 23(8 行受影响)*/
go
drop table tb,#