2020年审计师考试
-
考试报名：
5月25日-6月29日
北京、福建延后至7月 -
准考证打印：
9月27日-30日
10月9日-11日 -
考试时间：
10月11日 -
成绩公布：
12月11日前 -
成绩复核申诉：
自成绩公布之日起
30日内 -
证书领取：
以各地公告为准
巧用SQL技术审计审查【保障房审计】案例干货
来自 审计精选 发布时间：2019-05-09
随着信息化时代的来临，基层审计机关在审计工作中向被审计单位采集和处理的数据越来越多，但采集的数据普遍存在零星分散、规范性较差的特点。为提高审计效率，下面简要介绍两种常用不规范业务数据的处理技巧。
一、房产信息表身份证号字段多身份证号拆分
（一）项目背景
保障房审计项目中，为了查找违规享受廉租住房保障人员审计疑点，我们获取的房屋登记信息数据发现存在多人拥有同一套房屋所有权，其中房屋登记信息中身份证号码字段系多人身份证号码的情况，如“身份证：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,字段名为:房屋拥有者
表名为:房产信息部分字段(演示),字段名为:房屋拥有者
表名为:地税数据,字段名为:法定代表人姓名
表名为:工商数据,字段名为:法定代表人
通过以上方式即可快速找到未知数据库中是否存在我们审计所需的内容。
■ 中国审计师考试网（www.shenjishi.com）部分内容转载自网络，旨在传递更多审计师考试、审计行业和财经领域等相关信息，均尽力标明作者和出处。如您认为本站刊载内容涉及版权问题或者侵犯了您的合法权益，请通过本站提供的方式联系我们，我们将在核实确认后会尽快予以处理。
- 上一篇：【信息化审计技术】让金融审计更简单
- 下一篇：【审计云】信息化审计：破解审计数字制约
