巧用SQL整理不规范业务数据
| 随着信息化时代的来临，基层审计机关在审计工作中向被审计单位采集和处理的数据越来越多，但采集的数据普遍存在零星分散、规范性较差的特点。为提高审计效率，下面简要介绍两种常用不规范业务数据的处理技巧。 一、房产信息表身份证号字段多身份证号拆分 （一）项目背景 保障房审计项目中，为了查找违规享受廉租住房保障人员审计疑点，我们获取的房屋登记信息数据发现存在多人拥有同一套房屋所有权，其中房屋登记信息中身份证号码字段系多人身份证号码的情况，如“身份证：61022619****6890，身份证：61022619****5569”。为将享受廉租住房人员信息与房屋登记信息通过身份证号码进行关联分析，我们就需要把房屋登记信息按身份证号码字段分割成同一房屋多条记录。 （二）、数据源 因数据保密需要，此次仅选取房产登记信息中“[房屋拥有者]”和“[身份证号码]”两个字段举例，在实际审计工作中中可根据需要自行确定保留字段。实例数据库名称为“[测试数据]”，表名为“[房产信息部分字段]”。 （三）、数据处理方法 1、在“[测试数据]”数据库中新建[F_Get_Number]函数（同一数据库下仅执行一次以下脚本） CREATE function [dbo].[F_Get_Number] (@S varchar(100)) returns varchar(100) AS begin while PATINDEX('%[^0-9X]%',@S)>0 begin set @s=stuff(@s,patindex('%[^0-9X]%',@s),1,'') end return cast(@S as varchar(100)) end 2、对房屋登记信息以“身份证号码”字段进行拆分 建立每行记录的唯一值（采集的数据可能没有唯一标识符） if exists(select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#_test')) drop table #_test SELECT NEWID() id,[房屋拥有者],[身份证号码]—-中文字段为采集数据表的字段 into #_test FROM [测试数据].[dbo].[房产信息部分字段];--写入临时表#_test （2）对数据进行预处理 if exists(select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#_test2')) drop table #_test2; with mycte as ( SELECT ID,CAST('<i>' + REPLACE([身份证号码], '，', '</i><i>') + '</i>' AS XML) AS KID FROM [测试数据].[dbo].[#_test] ) select ID,dbo.F_Get_Number( replace(x.i.value('.', 'VARCHAR(100)'),'：','')) AS card_id --运用自定义函数[dbo].[F_Get_Number] into [测试数据].[dbo].#_test2 from mycte CROSS APPLY KID.nodes('i') x(i) where len(x.i.value('.', 'VARCHAR(100)'))<>'' --以关键字符分隔列名[身份证号码] （3）生成分割后的房产信息数据 if exists (select * from sys.objects where name='result' and type='U') drop table result SELECT b.card_id,a.* into result FROM [测试数据].[dbo].[#_test] a left join [测试数据].[dbo].[#_test2] b on a.id=b.id （4）查看分割后的数据结果 select * from result 将房屋登记信息处理后的结果表与享受廉租住房人员关联，发现153人涉嫌违规享受廉租住房。 二、全库关键字检索 （一）项目背景 平常我们将被审计单位采集的财务或业务数据库备份数据还原到审计数据平台后，如果对方单位没有提供数据字典，数据表名或字段像“AC01”等辨识程度不高的命名方式时，对我们下一步开展审计工作形成了一定的障碍，为提高审计效率，我们采取以下方式快速获取所需表名或表字段。 （二）数据处理方法（将以下脚本复制到所需查找的关键信息的数据库中） 1、对所需查找的关键信息的数据库新建并复制以下内容到“查询”文件中。 declare @关键字 varchar(500) declare @类型 varchar(50) set @关键字='陈' –-输入需要查找的关键字 set @类型='char' --------------------------------------------- declare @cloumns varchar(500) declare @tablename varchar(500) declare @str varchar(500) declare @counts int declare @sql nvarchar(2000) declare MyCursor Cursor For Select a.name as Columns, b.name as TableName from syscolumns a,sysobjects b,systypes c where a.id = b.id and b.type = 'U' and a.xtype=c.xtype and c.name like '%'+@类型+'%' set @str=@关键字 Open MyCursor Fetch next From MyCursor Into @cloumns,@tablename While(@@Fetch_Status = 0) Begin set @sql='select @tmp_counts=count(*) from [' +@tablename+ '] where [' +@cloumns+'] like ''%' +@str+ '%'''--模糊匹配 execute sp_executesql @sql,N'@tmp_counts int out',@counts out if @counts>0 begin print '表名为:'+@tablename+',字段名为:'+@cloumns end Fetch next From MyCursor Into @cloumns,@tablename End Close MyCursor Deallocate MyCursor 3、在上面脚本第三行输入关键字信息，如：set @关键字='陈'。 4、执行查询脚本。 输出结果如下： 表名为:房产信息部分字段,字段名为:房屋拥有者 表名为:result,字段名为:房屋拥有者 表名为:房产信息部分字段(演示),字段名为:房屋拥有者 表名为:地税数据,字段名为:法定代表人姓名 表名为:工商数据,字段名为:法定代表人 通过以上方式即可快速找到未知数据库中是否存在我们审计所需的内容。（石磊） |