【原创】处理冗余记录解决方案1:查询某些字段重复的SQL
表 table1
字段 id,name,time,column,commnet
记录 1,aaa,111,03,xyz
记录 2,bbb,231,01,xyz
记录 3,aaa,111,03,xyz
记录 4,aaa,111,03,xyz
记录 5,aaa,111,03,xyz
记录 6,aaa,111,03,xyz
记录 7,ccc,111,03,xyz
发现记录1、3、4、5、6是冗余记录
最近发现库中某表有很多这样的冗余记录(比上面例子更严重的情况。恩,1万多条冗余记录-.-')
领导急了!
怎么办?
来,让我们冷静的想一想......
首先,现在我们要查出所有这些捣蛋鬼——冗余记录!
行动起来!先写SQL吧,可能很多朋友立即想到distinct
在此说明一下distinct,他返回的是对象中唯一的记录(如果有表中存在自增字段,那么select distinct * from table与select * from table返回的结果是相同的),而我们需要查询的是出现次数超过1的记录。
我们先要用到having而不是distinct。(至于distinct,后面我们肯定也会用到)
SQL:
1
select
2
tmp.prgname,--会出现重复的字段
3
tmp.playtime,--会出现重复的字段
4
a.prgid,--自增标识字段
5
a.prgcolumn,--会出现重复的字段
6
a.prgcomment --会出现重复的字段
7
from ax_program a
8
left join
9
( Select
10
prgname,
11
playtime,
12
prgcolumn
13
From ax_program
14
group by prgname,playtime,prgcolumn
15
having count(*)>1 ) --加入出现超过1次的限制
16
tmp
17
on
18
a.prgname=tmp.prgname and a.playtime=tmp.playtime
19
where tmp.prgname is not null--筛选掉无用记录
20
group by tmp.prgname,tmp.playtime,a.prgcolumn ,a.prgid,a.prgcomment
21
order by tmp.prgname,tmp.playtime
注:select中出现prgid字段纯粹是为了传给程序需要的DataSet作为DataGrid的DataKeyFild用
![](/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)