审计AO中核实“excel表”虚假身份证信息sql语句:
SELECT * FROM
(SELECT 姓名, 身份证号,
Right(身份证号,1) AS 身份证校验码,
cast(sum(cast(substring(身份证号,1,1) as int)*7
+cast(substring(身份证号,2,1) as int)*9
+cast(substring(身份证号,3,1) as int)*10
+cast(substring(身份证号,4,1) as int)*5
+cast(substring(身份证号,5,1) as int)*8
+cast(substring(身份证号,6,1) as int)*4
+cast(substring(身份证号,7,1) as int)*2
+cast(substring(身份证号,8,1) as int)*1
+cast(substring(身份证号,9,1) as int)*6
+cast(substring(身份证号,10,1) as int)*3
+cast(substring(身份证号,11,1) as int)*7
+cast(substring(身份证号,12,1) as int)*9
+cast(substring(身份证号,13,1)as int)*10
+cast(substring(身份证号,14,1) as int)*5
+cast(substring(身份证号,15,1)as int)*8
+cast(substring(身份证号,16,1) as int)*4
+cast(substring(身份证号,17,1) as int)*2) % 11 as varchar) as 校验码
,b.对应校验码
from [学生名单1$] a LEFT JOIN [身份证校验对照表$] b ON Right(a.身份证号,1) = b.身份证校验码 WHERE Len(身份证号)=18
GROUP BY 姓名,
身份证号,
Right(a.身份证号,1),
b.对应校验码
) c
where c.校验码<>c.对应校验码
执行错误提示:
消息 16,级别 8114,状态 5,第 1 行: 从数据类型 nvarchar 转换为 float 时出错。
用语句如何实现?新手,详细点哦!