当前位置： 移动技术网 > IT编程>数据库>MSSQL > sql分组后二次汇总(处理表重复记录查询和删除)的实现方法

sql分组后二次汇总(处理表重复记录查询和删除)的实现方法

2017年12月08日  | 移动技术网IT编程  | 我要评论

胡诺言 陈琪,享利宝,编者的话400字

--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
1、num、name相同的重复值记录,没有大小关系只保留一条
2、name相同,id有大小关系时,保留大或小其中一个记录
整理人：中国风(roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,sql2005用row_number函数处理)

--> --> (roy)生成測試數據
 

if not object_id('tempdb..#t') is null
 drop table #t
go
create table #t([id] int,[name] nvarchar(1),[memo] nvarchar(2))
insert #t
select 1,n'a',n'a1' union all
select 2,n'a',n'a2' union all
select 3,n'a',n'a3' union all
select 4,n'b',n'b1' union all
select 5,n'b',n'b2'
go


--i、name相同id最小的记录(推荐用1,2,3),方法3在sql05时，效率高于1、2
方法1:
select * from #t a where not exists(select 1 from #t where name=a.name and id<a.id)

方法2:
select a.* from #t a join (select min(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id

方法3:
select * from #t a where id=(select min(id) from #t where name=a.name)

方法4:
select a.* from #t a join #t b on a.name=b.name and a.id>=b.id group by a.id,a.name,a.memo having count(1)=1

方法5:
select * from #t a group by id,name,memo having id=(select min(id)from #t where name=a.name)

方法6:
select * from #t a where (select count(1) from #t where name=a.name and id<a.id)=0

方法7:
select * from #t a where id=(select top 1 id from #t where name=a.name order by id)

方法8:
select * from #t a where id!>all(select id from #t where name=a.name)

方法9(注:id为唯一时可用):
select * from #t a where id in(select min(id) from #t group by name)

--sql2005:

方法10:
select id,name,memo from (select *,min(id)over(partition by name) as minid from #t a)t where id=minid

方法11:

select id,name,memo from (select *,row_number()over(partition by name order by id) as minid from #t a)t where minid=1

生成结果:
/*
id          name memo
----------- ---- ----
1           a    a1
4           b    b1

(2 行受影响)
*/


--ii、name相同id最大的记录,与min相反:
方法1:
select * from #t a where not exists(select 1 from #t where name=a.name and id>a.id)

方法2:
select a.* from #t a join (select max(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id order by id

方法3:
select * from #t a where id=(select max(id) from #t where name=a.name) order by id

方法4:
select a.* from #t a join #t b on a.name=b.name and a.id<=b.id group by a.id,a.name,a.memo having count(1)=1

方法5:
select * from #t a group by id,name,memo having id=(select max(id)from #t where name=a.name)

方法6:
select * from #t a where (select count(1) from #t where name=a.name and id>a.id)=0

方法7:
select * from #t a where id=(select top 1 id from #t where name=a.name order by id desc)

方法8:
select * from #t a where id!<all(select id from #t where name=a.name)

方法9(注:id为唯一时可用):
select * from #t a where id in(select max(id) from #t group by name)

--sql2005:

方法10:
select id,name,memo from (select *,max(id)over(partition by name) as minid from #t a)t where id=minid

方法11:
select id,name,memo from (select *,row_number()over(partition by name order by id desc) as minid from #t a)t where minid=1

生成结果2:
/*
id          name memo
----------- ---- ----
3           a    a3
5           b    b2

(2 行受影响)
*/

--2、删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (roy)生成測試數據

if not object_id('tempdb..#t') is null
    drop table #t
go
create table #t([id] int,[name] nvarchar(1),[memo] nvarchar(2))
insert #t
select 1,n'a',n'a1' union all
select 2,n'a',n'a2' union all
select 3,n'a',n'a3' union all
select 4,n'b',n'b1' union all
select 5,n'b',n'b2'
go

--i、name相同id最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #t a where  exists(select 1 from #t where name=a.name and id<a.id)

方法2:
delete a  from #t a left join (select min(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id where b.id is null

方法3:
delete a from #t a where id not in (select min(id) from #t where name=a.name)

方法4(注:id为唯一时可用):
delete a from #t a where id not in(select min(id)from #t group by name)

方法5:
delete a from #t a where (select count(1) from #t where name=a.name and id<a.id)>0

方法6:
delete a from #t a where id<>(select top 1 id from #t where name=a.name order by id)

方法7:
delete a from #t a where id>any(select id from #t where name=a.name)

select * from #t

生成结果:
/*
id          name memo
----------- ---- ----
1           a    a1
4           b    b1

(2 行受影响)
*/


--ii、name相同id保留最大的一条记录:

方法1:
delete a from #t a where  exists(select 1 from #t where name=a.name and id>a.id)

方法2:
delete a  from #t a left join (select max(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id where b.id is null

方法3:
delete a from #t a where id not in (select max(id) from #t where name=a.name)

方法4(注:id为唯一时可用):
delete a from #t a where id not in(select max(id)from #t group by name)

方法5:
delete a from #t a where (select count(1) from #t where name=a.name and id>a.id)>0

方法6:
delete a from #t a where id<>(select top 1 id from #t where name=a.name order by id desc)

方法7:
delete a from #t a where id<any(select id from #t where name=a.name)


select * from #t
/*
id          name memo
----------- ---- ----
3           a    a3
5           b    b2

(2 行受影响)
*/

--3、删除重复记录没有大小关系时，处理重复值


--> --> (roy)生成測試數據
 
if not object_id('tempdb..#t') is null
    drop table #t
go
create table #t([num] int,[name] nvarchar(1))
insert #t
select 1,n'a' union all
select 1,n'a' union all
select 1,n'a' union all
select 2,n'b' union all
select 2,n'b'
go

方法1:
if object_id('tempdb..#') is not null
    drop table #
select distinct * into # from #t--排除重复记录结果集生成临时表#

truncate table #t--清空表

insert #t select * from #    --把临时表#插入到表#t中

--查看结果
select * from #t

/*
num         name
----------- ----
1           a
2           b

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:

alter table #t add id int identity--新增标识列
go
delete a from  #t a where  exists(select 1 from #t where num=a.num and name=a.name and id>a.id)--只保留一条记录
go
alter table #t drop column id--删除标识列

--查看结果
select * from #t

/*
num         name
----------- ----
1           a
2           b

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare roy_cursor cursor local for
select count(1)-1,num,name from #t group by num,name having count(1)>1
declare @con int,@num int,@name nvarchar(1)
open roy_cursor
fetch next from roy_cursor into @con,@num,@name
while @@fetch_status=0
begin
    set rowcount @con;
    delete #t where num=@num and name=@name
    set rowcount 0;
    fetch next from roy_cursor into @con,@num,@name
end
close roy_cursor
deallocate roy_cursor

--查看结果
select * from #t
/*
num         name
----------- ----
1           a
2           b

(2 行受影响)

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网