当前位置: 首页 > 审计论坛

巧用SQL根据学生身份证号精确计算真实年龄

作者:邓鹏鹏   来源:本站      时间:2019-04-17

学生资助资金审计,由于涉及学生人数多、数据量大,运用大数据进行比对分析,成为重要的审计手段。笔者在审计中发现,年龄异常的学生领取学生资助资金,是虚报冒领的重要方式之一,而运用大数据进行比对分析,查找年龄异常学生的方式很多。通过尝试,笔者发现了一种巧用SQL筛选年龄异常学生的方法,效果很好。

首先,我们将从教育部门获取的2017年助学金发放表导入到SQL中

图片3.png

如上图所示,将采集到的助学金发放表excel导入到sql2008中,利用substring函数从身份证号码字段获取学生出生年份:substring([学生身份证号],7,4)(补充完整语句,并生成新表,新表取表名)

接下来利用sql 时间函数datediff() 来获取学生年龄,由于审计的是2017年助学金,编写sql语句如下:

select [行政区划代码],[行政区划名称] ,[学校标识码] ,[学校名称],[发放学期],[学生姓名],[学生身份证号]

,datediff(year,substring([学生身份证号],7,4),'2017') 年龄 

,[学籍号],[年级班级],[家庭住址],[减免金额()] ,[减免日期],[发放银行卡号],[发放银行名称],[受助类型] 

FROM [master].[dbo].学生享受普通高中免学杂费  

图片4.png

笔者通过观察发放表字段发放学期和身份号字段发现,资金发放时间是2017年春季,但大部分学生出生时间在2017年下半年。这样就出现一个问题,我们刚算出来的年龄并非学生领取助学金时的真实周岁年龄,例如一个学生,出生在199712月,而春季发放资金是在20173月,此时学生未满20岁,但是通过上述sql语句却得到结果是20岁,而正常高中就读年龄一般在18-19周岁之间,由于语句编写不合理,从而将正常就读年龄学生划入核查疑点,会导致核查工作量的增加。

那如何解决这个问题?我们将上述sql语句进行稍许修改,巧用一个FLOOR函数

FLOOR函数解释:返回小于或等于expr的最大整数.FLOOR(1.1)返回1,FLOOR(-1.1)返回-2,FLOOR(1)返回1)就能解决上述问题。重新编写sql语句如下:

Select [行政区划代码],[行政区划名称] ,[学校标识码] ,[学校名称],[发放学期],[学生姓名],[学生身份证号]

,floor(datediff(dy,substring([学生身份证号],7,4)+'-'+substring([学生身份证号],11,2)+'-'+substring([学生身份证号],13,2),'2017-03-01')/365) 年龄

,[学籍号],[年级班级],[家庭住址],[减免金额()] ,[减免日期],[发放银行卡号],[发放银行名称],[受助类型]FROM [master].[dbo].学生享受普通高中免学杂费

图片5.png

结果对比发现,此时计算出的学生年龄才是发放助学金时学生的实际周岁。

古人云:差之毫厘,谬之千里。虽然查询结果相差只有一岁,但是得到的结论却相差很远。所以审计人员在实际工作中如何正确应用sql语句查询疑点至关重要,不仅可以减少现场核实工作量,也可以大大降低审计风险。