求教找出目录下文件内容中完全重复的行

chiyiangel

在一个目录下 有很多子目录 (很多很多)
里面的文件全是sql 脚本 大部分是insert create update语句
但是有很多重复的
请问如何找出这些sql脚本中完全重复的语句呢
找出完全重复的sql语句然后输出
PS：找出重复的sql语句后 还要把路径找出来


比如 aaa/bbb/1.sql 中有一条语句和 ccc/ddd/2.sql 中的一条重复了
要输出这条重复的语句然后还需要把 aaa/bbb/1.sql 和ccc/ddd/2.sql
这两个路径输出出来

我实在找不到办法了  请各位高手看看

1. insert into pub_billtemplet(bill_templetcaption,bill_templetname,dr,funccode,metadataclass,model_type,modulecode,nodecode,options,pk_billtemplet,pk_billtypecode,pk_corp,resid,shareflag,ts,validateformula) values('单据-资产实体版本','SYSTEM',0,'~','ncaam.appassetversion',null,'NCAAM','',null,'1027Z1100000000005G5','1411AAM_V','@@@@','~',null,'2010-06-18 15:55:15',null)
   
2. go
   
3. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,1,null,-1,'N',null,-1,'vno',0,'N',1,'Y',null,0,'vno','ncaam.appassetversion.vno',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005G8','@@@@',0,null,null,null,'N',1,3,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
4. go
   
5. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'creationtime',0,'N',1,'Y',null,0,'creationtime','ncaam.appassetversion.creationtime',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005G9','@@@@',2,null,null,null,'N',1,2,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
6. go

复制代码

1. insert into pub_billtemplet_t(basetab,dr,metadataclass,metadatapath,mixindex,pk_billtemplet,pk_billtemplet_t,pk_layout,pos,position,resid,tabcode,tabindex,tabname,ts,vdef1,vdef2,vdef3) values(null,0,'ncaam.appassetversion',null,null,'1027Z1100000000005G5','1004Z11000000000LEUD','~',0,0,null,'appassetversionHVO',0,'应用资产版本表头','2010-06-18 15:55:15',null,null,null)
   
2. go
   
3. insert into pub_billtemplet_t(basetab,dr,metadataclass,metadatapath,mixindex,pk_billtemplet,pk_billtemplet_t,pk_layout,pos,position,resid,tabcode,tabindex,tabname,ts,vdef1,vdef2,vdef3) values(null,0,'ncaam.appassetversion',null,null,'1027Z1100000000005G5','1004Z11000000000LEUE','~',2,null,null,'audit',0,'审计信息','2010-06-18 15:55:15',null,null,null)
   
4. go
   
5. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'creator',0,'N',1,'Y',null,0,'creator','ncaam.appassetversion.creator',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GD','@@@@',2,null,null,null,'N',1,1,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
6. go
   
7. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'modifier',0,'N',1,'Y',null,0,'modifier','ncaam.appassetversion.modifier',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GE','@@@@',2,null,null,null,'N',1,3,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
8. go
   
9. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'pk_asset',0,'N',1,'N',null,0,'pk_asset','ncaam.appassetversion.pk_asset',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GF','@@@@',0,null,null,null,'N',0,2,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
10. go
    

复制代码

1. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,1,null,-1,'N',null,-1,'ts',0,'N',1,'N',null,0,'ts','ncaam.appassetversion.ts',null,'N',0,null,'1027Z1100000000005G5','1004Z11000000000LCKD','@@@@',2,null,null,null,'N',0,5,'audit','audit',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
2. go
   
3. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'pk_version',0,'N',1,'N',null,0,'pk_version','ncaam.appassetversion.pk_version',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005G6','@@@@',0,null,null,null,'N',0,1,'appassetversionHVO','应用资产版本表头',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
4. go
   
5. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'modifiedtime',0,'N',1,'Y',null,0,'modifiedtime','ncaam.appassetversion.modifiedtime',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005G7','@@@@',2,null,null,null,'N',1,4,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
6. go
   
7. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,1,null,-1,'N',null,-1,'vno',0,'N',1,'Y',null,0,'vno','ncaam.appassetversion.vno',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005G8','@@@@',0,null,null,null,'N',1,3,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
8. go
   
9. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'creationtime',0,'N',1,'Y',null,0,'creationtime','ncaam.appassetversion.creationtime',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005G9','@@@@',2,null,null,null,'N',1,2,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
   
10. go
    
11. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,1,null,-1,'N',null,-1,'vname',0,'N',1,'Y',null,0,'vname','ncaam.appassetversion.vname',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GA','@@@@',0,null,null,null,'N',1,4,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
    
12. go
    
13. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,1,null,-1,'N',null,-1,'vstartdate',0,'N',1,'Y',null,0,'vstartdate','ncaam.appassetversion.vstartdate',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GB','@@@@',0,null,null,null,'N',1,5,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
    
14. go
    
15. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,1,null,-1,'N',null,-1,'venddate',0,'N',1,'Y',null,0,'venddate','ncaam.appassetversion.venddate',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GC','@@@@',0,null,null,null,'N',1,6,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
    
16. go
    
17. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'creator',0,'N',1,'Y',null,0,'creator','ncaam.appassetversion.creator',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GD','@@@@',2,null,null,null,'N',1,1,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
    
18. go
    
19. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'modifier',0,'N',1,'Y',null,0,'modifier','ncaam.appassetversion.modifier',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GE','@@@@',2,null,null,null,'N',1,3,'audit','审计信息',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
    
20. go
    
21. insert into pub_billtemplet_b(cardflag,datatype,defaultshowname,defaultvalue,dr,editflag,editformula,foreground,hyperlinkflag,idcolname,inputlength,itemkey,itemtype,leafflag,listflag,listshowflag,loadformula,lockflag,metadatapath,metadataproperty,metadatarelation,newlineflag,nullflag,options,pk_billtemplet,pk_billtemplet_b,pk_corp,pos,reftype,resid,resid_tabname,reviseflag,showflag,showorder,table_code,table_name,totalflag,ts,userdefflag,userdefine1,userdefine2,userdefine3,usereditflag,userflag,userreviseflag,usershowflag,validateformula,width) values(1,-1,null,null,0,0,null,-1,'N',null,-1,'pk_asset',0,'N',1,'N',null,0,'pk_asset','ncaam.appassetversion.pk_asset',null,'N',0,null,'1027Z1100000000005G5','1027Z1100000000005GF','@@@@',0,null,null,null,'N',0,2,'appassetversionHVO','appassetversionHVO',0,'2010-06-18 15:55:15','N',null,null,null,1,1,'N',1,null,1)
    
22. go
    

复制代码

ywlscpl

#find出所有.sql文件到一个列表文件list
find . -name "*.sql" -type f -print >list
for file in `cat list`
do
   #把$file的每一行内容跟除自己外的其他文件每一行比较
  for file1 in `cat list |grep -v $file`
   do
     .....
   done
done

chiyiangel

回复 2# ywlscpl


    非常感谢...  但是现在光 sql脚本的文件就2W 多个 然后每个文件内有 不下50条的sql
语句   而且最关键的目的在于   我要找出 有哪些文件内 有相同的sql 语句然后好把他们给摘出来 做调整

ywlscpl

干脆写一个
comm.sh

1. #!/bin/bash
   
2. find . -name "*.sql" -type f -print >list
   
3. for file in `cat list`
   
4. do
   
5.   for file1 in `cat list |grep -v $file`
   
6.   do
   
7.     awk 'NR==FNR{a[NR]=$0;f=FILENAME}NR>FNR{for (i in a) if (a[i]==$0) print FILENAME":line "FNR" ==>"f":line "i}' $file $file1
   
8.   done
   
9. done

复制代码

1. ywlscpl@ubuntu:~$ cat 1.sql
   
2. 1
   
3. 2
   
4. 3
   
5. 4
   
6. 5
   
7. ywlscpl@ubuntu:~$ cat 3.sql
   
8. 4
   
9. 5
   
10. 6
    
11. 7
    
12. 8
    
13. ywlscpl@ubuntu:~$ cat d1/2.sql
    
14. 3
    
15. 4
    
16. 5
    
17. 6
    
18. 7
    
19. ywlscpl@ubuntu:~$ sh comm.sh
    
20. ./d1/2.sql:line 1 ==>./1.sql:line 3
    
21. ./d1/2.sql:line 2 ==>./1.sql:line 4
    
22. ./d1/2.sql:line 3 ==>./1.sql:line 5
    
23. ./3.sql:line 1 ==>./1.sql:line 4
    
24. ./3.sql:line 2 ==>./1.sql:line 5
    
25. ./1.sql:line 3 ==>./d1/2.sql:line 1
    
26. ./1.sql:line 4 ==>./d1/2.sql:line 2
    
27. ./1.sql:line 5 ==>./d1/2.sql:line 3
    
28. ./3.sql:line 1 ==>./d1/2.sql:line 2
    
29. ./3.sql:line 2 ==>./d1/2.sql:line 3
    
30. ./3.sql:line 3 ==>./d1/2.sql:line 4
    
31. ./3.sql:line 4 ==>./d1/2.sql:line 5
    
32. ./1.sql:line 4 ==>./3.sql:line 1
    
33. ./1.sql:line 5 ==>./3.sql:line 2
    
34. ./d1/2.sql:line 2 ==>./3.sql:line 1
    
35. ./d1/2.sql:line 3 ==>./3.sql:line 2
    
36. ./d1/2.sql:line 4 ==>./3.sql:line 3
    
37. ./d1/2.sql:line 5 ==>./3.sql:line 4

复制代码

chiyiangel

回复 4# ywlscpl


    大哥威武 非常感谢  现在又有一个新问题  因为在每行语句后面都有一个go ...  能不能忽略掉 这个go (因为是sqlserver 的脚本)
再一次感谢

ywlscpl

回复 5# chiyiangel

把重复的结果过滤了一下
comm.sh

1. #!/bin/bash
   
2. DIR="."
   
3. find $DIR -name "*.sql" -type f -print >list
   
4. for file in `cat list`
   
5. do
   
6.   for file1 in `cat list |grep -v $file`
   
7.   do
   
8.     awk '/^ *go *$/{next}NR==FNR{a[NR]=$0;f=FILENAME}NR>FNR{for (i in a) if (a[i]==$0) print FILENAME":line "FNR"==>"f":line "i}' $file $file1
   
9.   done
   
10. done | awk -F '==>' '!a[$1$2]++&&!a[$2$1]++'

复制代码

chiyiangel

回复 6# ywlscpl


    是这样的   在sql 脚本中的格式是这样的

1. insert xxx  into xxxxxxxxxxxxx
   
2. go

复制代码

每个脚本里每一句语句后面都会有新起的一行然后有一个go 就跟上面的一样
    用那个脚本的话 会把所有 go 相同的也给过滤出来

ywlscpl

回复 7# chiyiangel


    6楼的已根据你说的改了

chiyiangel

回复 8# ywlscpl


    非常感谢大哥 的热心 谢谢

chiyiangel

回复 8# ywlscpl

用了 6楼的脚本 还是 无法过滤出来 ... 我传了几个样本上来  麻烦帮忙看一下  非常感谢