【原创】处理冗余记录解决方案2:编写智能删除冗余记录的存储过程
续1。
现在有了查找所有重复记录的SQL,接下来怎么做?
直接删除他们么?
不!那样连想要的记录也一起删掉了,至少要分别保留一条记录!
一条SQL语句恐怕达不到目的。恩,现在需要临时表,然后可以用视图,最好还是写在存储过程里
本来想写解决方案3的,想想也没什么特别的,姑且都写在这里吧!
在数据层类库加入两个方法,查询和删除冗余数据
现在有了查找所有重复记录的SQL,接下来怎么做?
直接删除他们么?
不!那样连想要的记录也一起删掉了,至少要分别保留一条记录!
一条SQL语句恐怕达不到目的。恩,现在需要临时表,然后可以用视图,最好还是写在存储过程里
1
SET QUOTED_IDENTIFIER ON
2
GO
3
SET ANSI_NULLS ON
4
GO
5![](/Images/OutliningIndicators/None.gif)
6
CREATE procedure dbo.Ax_delSame
7
8
as
9
set nocount on
10
11
if object_id('#tmp1') is not null
12
drop table #tmp1
13
if object_id('#tmp2') is not null
14
drop table #tmp2
15
--先把所有冗余记录找出来,临时表#tmp1存放冗余记录
16
select * into #tmp1 from
17
(
18
select a.prgid,tmp.prgname,tmp.playtime,a.prgcolumn,a.prgcomment
19
from ax_program a
20
left join (Select prgname,playtime,prgcolumn --含有重复值prgname,playtime,prgcolumn的记录为想找的冗余记录
21
From ax_program group by prgname,playtime,prgcolumn having count(*)>1 ) tmp
22
on a.prgname=tmp.prgname and a.playtime=tmp.playtime
23
where tmp.prgname is not null
24
group by tmp.prgname,tmp.playtime,a.prgcolumn ,a.prgid,a.prgcomment
25
) as checktmp
26![](/Images/OutliningIndicators/None.gif)
27
select * into #tmp2 from (select distinct prgname,playtime,prgcolumn from #tmp1 ) tmp --将们最终想要保留的结果保存到#tmp2
28![](/Images/OutliningIndicators/None.gif)
29
delete from ax_program where prgid in(select prgid from #tmp1 )--将所有冗余记录删除!
30![](/Images/OutliningIndicators/None.gif)
31
insert into ax_program (prgname,playtime,prgcolumn ) select * from #tmp2 --将#tmp2全部导入要操作表
32![](/Images/OutliningIndicators/None.gif)
33![](/Images/OutliningIndicators/None.gif)
34
return isnull(@@identity,0) --返回操作行数供ADO.NET使用
35![](/Images/OutliningIndicators/None.gif)
36
GO
37
SET QUOTED_IDENTIFIER OFF
38
GO
39
SET ANSI_NULLS ON
40
GO
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
本来想写解决方案3的,想想也没什么特别的,姑且都写在这里吧!
在数据层类库加入两个方法,查询和删除冗余数据
1
/// <summary>
2
/// 检查重复记录
3
/// </summary>
4
/// <returns>返回一个数据集</returns>
5
public DataSet GetSameData()
6
{
7
DataSet ds=new DataSet();
8
//执行存储过程Ax_checkSame,该存储过程返回select结果集装入DataSet
9
SqlDataAdapter da = new SqlDataAdapter("Ax_checkSame", SqlConnection);
10![](/Images/OutliningIndicators/InBlock.gif)
11
SqlConnection conn = new SqlConnection(SqlConnection);
12
da.SelectCommand.CommandType=CommandType.StoredProcedure;
13![](/Images/OutliningIndicators/InBlock.gif)
14
15
try
16
{
17
conn.Open();
18
da.Fill(ds);
19![](/Images/OutliningIndicators/InBlock.gif)
20
21
}
22
catch (SqlException ex)
23
{
24![](/Images/OutliningIndicators/InBlock.gif)
25
throw new Exception(ex.Message, ex);
26
}
27
finally { conn.Close(); }
28
29
return ds;
30
}
31
/// <summary>
32
/// 删除冗余数据
33
/// </summary>
34
/// <returns>返回操作的行数</returns>
35
public int DeleteSame()
36
{
37
int r=0;
38
SqlConnection conn = new SqlConnection(SqlConnection);
39
SqlCommand com = new SqlCommand("Ax_delSame", conn);
40
//设置为执行存储过程Ax_delSame
41
com.CommandType = CommandType.StoredProcedure;
42
43
try
44
{
45
conn.Open();
46![](/Images/OutliningIndicators/InBlock.gif)
47
com.ExecuteNonQuery();
48
//存储过程执行结果即影响的行数作为方法返回值
49
r=(int)ParameterDirection.ReturnValue;
50
}
51
catch (SqlException ex)
52
{
53![](/Images/OutliningIndicators/InBlock.gif)
54
throw new Exception(ex.Message, ex);
55
}
56
finally { conn.Close(); }
57
return r;
58
}
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](/Images/OutliningIndicators/InBlock.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/InBlock.gif)
16
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/InBlock.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
24
![](/Images/OutliningIndicators/InBlock.gif)
25
![](/Images/OutliningIndicators/InBlock.gif)
26
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
27
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
28
![](/Images/OutliningIndicators/InBlock.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
31
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
32
![](/Images/OutliningIndicators/InBlock.gif)
33
![](/Images/OutliningIndicators/InBlock.gif)
34
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/InBlock.gif)
43
![](/Images/OutliningIndicators/InBlock.gif)
44
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/InBlock.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/InBlock.gif)
49
![](/Images/OutliningIndicators/InBlock.gif)
50
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/InBlock.gif)
55
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
56
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
57
![](/Images/OutliningIndicators/InBlock.gif)
58
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)