create table aaatest
(
aaa int,
bbb int,
ccc varchar(20)
)
goinsert into aaatest values(1,2,null)
insert into aaatest values(1,2,null)
insert into aaatest values(1,3,null)
insert into aaatest values(1,4,null)
insert into aaatest values(2,2,null)
insert into aaatest values(2,3,null)
insert into aaatest values(2,3,null)select aaa,max(bbb),ccc
from
(select * from aaatest
group by aaa,bbb,ccc
having count(1)>0 ) a
group by aaa,ccc
(
aaa int,
bbb int,
ccc varchar(20)
)
goinsert into aaatest values(1,2,null)
insert into aaatest values(1,2,null)
insert into aaatest values(1,3,null)
insert into aaatest values(1,4,null)
insert into aaatest values(2,2,null)
insert into aaatest values(2,3,null)
insert into aaatest values(2,3,null)select aaa,max(bbb),ccc
from
(select * from aaatest
group by aaa,bbb,ccc
having count(1)>0 ) a
group by aaa,ccc
(
aaa int,
bbb int,
ccc varchar(20)
)
goinsert into aaatest values(1,2,null)
insert into aaatest values(1,2,null)
insert into aaatest values(1,3,null)
insert into aaatest values(1,4,null)
insert into aaatest values(2,2,null)
insert into aaatest values(2,3,null)
insert into aaatest values(2,3,null)select * from aaatestselect distinct * from aaatest O where bbb in
(select max(bbb) from aaatest where aaa=O.aaa)
-测试结果
1 4 NULL
2 3 NULL
查询来说 smalldeer(晓露的方法是对的,
但是问题我不想把结果取出来,因为记录太多,几十万条,而是直接删除,除
1 4 NULL
2 3 NULL
以为的记录
查询来说 smalldeer(晓露的方法是对的,
但是问题我不想把结果取出来,因为记录太多,几十万条,而是直接删除,除
1 4 NULL
2 3 NULL
以外的记录
(select max(bbb) from eee where aaa=O.aaa)
delete from eeeinsert into eee
select * from #adrop table #a
from 表名
group by aaa,ccc
delete a
from eee a left join(
select aaa,bbb=max(bbb)
from eee
group by aaa
)b on a.aaa=b.aaa and a.bbb=b.bbb
where b.bbb is null
delete a
from eee a
where bbb<>(
select max(bbb)
from eee
where aaa=a.aaa)
的方法删不掉
1 4 NULL
2 3 NULL
2 3 NULL结果是:
1。删除重复底记录,
2。再留下按aaa分组,bbb最大底记录第二个好办,但你的重复记录是完全重复的
这样的重复记录最好用的就是
select distinct * into #a from eee
delete from eee
insert into eee
select * from #a
drop table #a
insert into aaatest values(1,2,null)
insert into aaatest values(1,2,null)
insert into aaatest values(1,3,null)
insert into aaatest values(1,4,null)
insert into aaatest values(2,2,null)
insert into aaatest values(2,3,null)
insert into aaatest values(2,3,null)
go--删除处理
alter table aaatest add id int identity(1,1)
godelete a
from aaatest a left join(
select id=max(id) from aaatest a join(
select aaa,bbb=max(bbb)
from aaatest group by aaa
)b on a.aaa=b.aaa and a.bbb=b.bbb
group by b.aaa,b.bbb
)b on a.id=b.id
where b.id is null
goalter table aaatest drop column id
go--显示删除处理结果
select * from aaatest
go--删除测试
drop table aaatest/*--测试结果aaa bbb ccc
----------- ----------- --------------------
1 4 NULL
2 3 NULL(所影响的行数为 2 行)--*/