本人刚学SQL Server不久,现在有一个需求要写存储语句,想了两天感觉写的跟狗屎一样所以来求助大神,求指点~
需求:写一个存储过程:我有两张表,一张新的,一张旧的,两张表列名一样,都包括了编号、姓名、证件类型、公司名称、更新时间(其中旧表包括多个更新2014、2015,新表只有2016),,,,而在身份证那一列中,新表有些人是15位身份证,现在我要写一个存储过程,把新表的15位身份证更新为18位身份证(转换函数网上有找),然后与旧表对比,如果旧表那个人的身份证是18位的,就更新 新表那个人15位的身份证为18位,,,如果旧表那个人的也是15位身份证,就不变更 新表中那个人的身份证号,,,该怎么写呢?
我觉得是这样:因为数据量大,直接更新表速度慢,所以先把新表中身份证号为15位的人的姓名、身份证号、公司名称等信息提取出来,再把旧表中跟提取出来的人的名字、公司名称等相同的人拿出来,两列身份证号(新、旧)逐个对比,如果两个身份证号不同(一个15位一个18位不同),就更改新表那个人15位身份证为18位,如果相同(新旧表都是15位身份证),就不对那个人操作,这样想对嘛?
写的时候写崩了想直接对比操作,结果不行,,,下面是我的代码:
--存储过程
Create Proc Change15iDTo18iD
--@ID15 char(15)
--@ID18 char(18)
As
Begin
SELECT distinct n. 编号
,n. 公司
,n. 姓名
,n.证件类型
,n.证件号
,n.更新时间
,o.编号
,o.公司
,o.姓名
,o. 证件类型
,o.证件号
,o. 更新时间
FROM 新表 AS n , 旧表s AS o
where LEN(n.证件号) = 15
and n.姓名 = o.姓名
and n.公司 = o.公司
and n.证件类型 = '身份证'
and o.证件类型= '身份证'
and n.证件号 <> o.证件号
order by n.更新时间 desc-----------------【这里遇到一个问题,因为旧表中更新时间有几个年份(每个年份内容一样),提取 的时候会把旧表中所有年份的列出来,导致新表出现重复】
--调用函数并更新 新表
update 新表
set
n.证件号 = dbo.ID15TO18(@ID18)
where LEN(n.证件号) = 15
and n.姓名 = o.姓名
and n.公司 = o.公司
and n.证件类型= '身份证'
and o.证件类型= '身份证'
and n.证件号 <> o.证件号 ----------------------------------【这里实在没想到怎么去筛选where条件,,,】
End--将15位身份证升级成18位的用户定义函数
CREATE FUNCTION ID15TO18 (@id15 char(15))
RETURNS CHAR(18) AS
BEGIN
DECLARE @ID18 CHAR(18)
DECLARE @S1 AS INTEGER
DECLARE @S2 AS INTEGER
DECLARE @S3 AS INTEGER
DECLARE @S4 AS INTEGER
DECLARE @S5 AS INTEGER
DECLARE @S6 AS INTEGER
DECLARE @S7 AS INTEGER
DECLARE @S8 AS INTEGER
DECLARE @S9 AS INTEGER
DECLARE @S10 AS INTEGER
DECLARE @S11 AS INTEGER
DECLARE @S12 AS INTEGER
DECLARE @S13 AS INTEGER
DECLARE @S14 AS INTEGER
DECLARE @S15 AS INTEGER
DECLARE @S16 AS INTEGER
DECLARE @S17 AS INTEGER
DECLARE @S18 AS INTEGER
SET @S1 = SUBSTRING(@ID15,1,1)
SET @S2 = SUBSTRING(@ID15,2,1)
SET @S3 = SUBSTRING(@ID15,3,1)
SET @S4 = SUBSTRING(@ID15,4,1)
SET @S5 = SUBSTRING(@ID15,5,1)
SET @S6 = SUBSTRING(@ID15,6,1)
SET @S7 = 1
SET @S8 = 9
SET @S9 = SUBSTRING(@ID15,7,1)
SET @S10 = SUBSTRING(@ID15,8,1)
SET @S11 = SUBSTRING(@ID15,9,1)
SET @S12 = SUBSTRING(@ID15,10,1)
SET @S13 = SUBSTRING(@ID15,11,1)
SET @S14 = SUBSTRING(@ID15,12,1)
SET @S15 = SUBSTRING(@ID15,13,1)
SET @S16 = SUBSTRING(@ID15,14,1)
SET @S17 = SUBSTRING(@ID15,15,1)
SET @S18 = ( (@S1*7) + (@S2*9) + (@S3*10) + (@S4*5) + (@S5*8) +
(@S6*4) + (@S7*2) + (@S8*1) + (@S9*6) + (@S10*3) +
(@S11*7) + (@S12*9) + (@S13*10) + (@S14*5) + (@S15*8) +
(@S16*4) + (@S17*2) ) % 11
SET @ID18 = SUBSTRING(@ID15,1,6) + '19' + SUBSTRING(@ID15,7,9) +
CASE
WHEN @S18 = 0 THEN '1'
WHEN @S18 = 1 THEN '0'
WHEN @S18 = 2 THEN 'X'
WHEN @S18 = 3 THEN '9'
WHEN @S18 = 4 THEN '8'
WHEN @S18 = 5 THEN '7'
WHEN @S18 = 6 THEN '6'
WHEN @S18 = 7 THEN '5'
WHEN @S18 = 8 THEN '4'
WHEN @S18 = 9 THEN '3'
WHEN @S18 = 10 THEN '2'
END
RETURN @ID18
END
【报错汇总:1、消息 137,级别 15,状态 2,过程 Change15iDTo18iD,第 1 行必须声明标量变量 "@ID15"。
2、消息 102,级别 15,状态 1,过程 Change15iDTo18iD,第 1 行'END' 附近有语法错误。
3、CREATE 必须在第一行声明
】
------------------------------------------------------------------------------------割---------------------------------------------------------------
具体情况大概就是这样,求大神指点,不胜感激
需求:写一个存储过程:我有两张表,一张新的,一张旧的,两张表列名一样,都包括了编号、姓名、证件类型、公司名称、更新时间(其中旧表包括多个更新2014、2015,新表只有2016),,,,而在身份证那一列中,新表有些人是15位身份证,现在我要写一个存储过程,把新表的15位身份证更新为18位身份证(转换函数网上有找),然后与旧表对比,如果旧表那个人的身份证是18位的,就更新 新表那个人15位的身份证为18位,,,如果旧表那个人的也是15位身份证,就不变更 新表中那个人的身份证号,,,该怎么写呢?
我觉得是这样:因为数据量大,直接更新表速度慢,所以先把新表中身份证号为15位的人的姓名、身份证号、公司名称等信息提取出来,再把旧表中跟提取出来的人的名字、公司名称等相同的人拿出来,两列身份证号(新、旧)逐个对比,如果两个身份证号不同(一个15位一个18位不同),就更改新表那个人15位身份证为18位,如果相同(新旧表都是15位身份证),就不对那个人操作,这样想对嘛?
写的时候写崩了想直接对比操作,结果不行,,,下面是我的代码:
--存储过程
Create Proc Change15iDTo18iD
--@ID15 char(15)
--@ID18 char(18)
As
Begin
SELECT distinct n. 编号
,n. 公司
,n. 姓名
,n.证件类型
,n.证件号
,n.更新时间
,o.编号
,o.公司
,o.姓名
,o. 证件类型
,o.证件号
,o. 更新时间
FROM 新表 AS n , 旧表s AS o
where LEN(n.证件号) = 15
and n.姓名 = o.姓名
and n.公司 = o.公司
and n.证件类型 = '身份证'
and o.证件类型= '身份证'
and n.证件号 <> o.证件号
order by n.更新时间 desc-----------------【这里遇到一个问题,因为旧表中更新时间有几个年份(每个年份内容一样),提取 的时候会把旧表中所有年份的列出来,导致新表出现重复】
--调用函数并更新 新表
update 新表
set
n.证件号 = dbo.ID15TO18(@ID18)
where LEN(n.证件号) = 15
and n.姓名 = o.姓名
and n.公司 = o.公司
and n.证件类型= '身份证'
and o.证件类型= '身份证'
and n.证件号 <> o.证件号 ----------------------------------【这里实在没想到怎么去筛选where条件,,,】
End--将15位身份证升级成18位的用户定义函数
CREATE FUNCTION ID15TO18 (@id15 char(15))
RETURNS CHAR(18) AS
BEGIN
DECLARE @ID18 CHAR(18)
DECLARE @S1 AS INTEGER
DECLARE @S2 AS INTEGER
DECLARE @S3 AS INTEGER
DECLARE @S4 AS INTEGER
DECLARE @S5 AS INTEGER
DECLARE @S6 AS INTEGER
DECLARE @S7 AS INTEGER
DECLARE @S8 AS INTEGER
DECLARE @S9 AS INTEGER
DECLARE @S10 AS INTEGER
DECLARE @S11 AS INTEGER
DECLARE @S12 AS INTEGER
DECLARE @S13 AS INTEGER
DECLARE @S14 AS INTEGER
DECLARE @S15 AS INTEGER
DECLARE @S16 AS INTEGER
DECLARE @S17 AS INTEGER
DECLARE @S18 AS INTEGER
SET @S1 = SUBSTRING(@ID15,1,1)
SET @S2 = SUBSTRING(@ID15,2,1)
SET @S3 = SUBSTRING(@ID15,3,1)
SET @S4 = SUBSTRING(@ID15,4,1)
SET @S5 = SUBSTRING(@ID15,5,1)
SET @S6 = SUBSTRING(@ID15,6,1)
SET @S7 = 1
SET @S8 = 9
SET @S9 = SUBSTRING(@ID15,7,1)
SET @S10 = SUBSTRING(@ID15,8,1)
SET @S11 = SUBSTRING(@ID15,9,1)
SET @S12 = SUBSTRING(@ID15,10,1)
SET @S13 = SUBSTRING(@ID15,11,1)
SET @S14 = SUBSTRING(@ID15,12,1)
SET @S15 = SUBSTRING(@ID15,13,1)
SET @S16 = SUBSTRING(@ID15,14,1)
SET @S17 = SUBSTRING(@ID15,15,1)
SET @S18 = ( (@S1*7) + (@S2*9) + (@S3*10) + (@S4*5) + (@S5*8) +
(@S6*4) + (@S7*2) + (@S8*1) + (@S9*6) + (@S10*3) +
(@S11*7) + (@S12*9) + (@S13*10) + (@S14*5) + (@S15*8) +
(@S16*4) + (@S17*2) ) % 11
SET @ID18 = SUBSTRING(@ID15,1,6) + '19' + SUBSTRING(@ID15,7,9) +
CASE
WHEN @S18 = 0 THEN '1'
WHEN @S18 = 1 THEN '0'
WHEN @S18 = 2 THEN 'X'
WHEN @S18 = 3 THEN '9'
WHEN @S18 = 4 THEN '8'
WHEN @S18 = 5 THEN '7'
WHEN @S18 = 6 THEN '6'
WHEN @S18 = 7 THEN '5'
WHEN @S18 = 8 THEN '4'
WHEN @S18 = 9 THEN '3'
WHEN @S18 = 10 THEN '2'
END
RETURN @ID18
END
【报错汇总:1、消息 137,级别 15,状态 2,过程 Change15iDTo18iD,第 1 行必须声明标量变量 "@ID15"。
2、消息 102,级别 15,状态 1,过程 Change15iDTo18iD,第 1 行'END' 附近有语法错误。
3、CREATE 必须在第一行声明
】
------------------------------------------------------------------------------------割---------------------------------------------------------------
具体情况大概就是这样,求大神指点,不胜感激
http://blog.csdn.net/roy_88/article/details/3898876
set
n.证件号 = dbo.ID15TO18(ID) -- 直接更新就可以 ,不用先查询
where LEN(n.证件号) = 15
,n. 公司
,n. 姓名
,n.证件类型
,dbo.F_Iden15To18(n.证件号) --转换18位
,n.更新时间
,o.编号
,o.公司
,o.姓名
,o. 证件类型
,o.证件号
,o. 更新时间
FROM 新表 AS n,(SELECT *,ROW_NUMBER()OVER(PARTITION BY 证件号 ORDER BY 更新时间) AS RN FROM 旧表s WHERE 证件类型= '身份证') AS o
where LEN(n.证件号) = 15 AND o.RN=1--取最近时间
and n.姓名 = o.姓名
and n.公司 = o.公司
and n.证件类型 = '身份证'
and dbo.F_Iden15To18(n.证件号) <> o.证件号
order by n.更新时间 desc--红色改动位置转换新表15号身份证
update 新表 SET 证件号=dbo.F_Iden15To18(证件号) WHERE LEN(证件号) = 15 AND 证件类型= '身份证'
在 过程、函数或触发器 中不允许使用 USE database 语句。】--存储过程
Create Proc Change15iDTo18iD
--@ID15 char(15)
--@ID18 char(18)
As
Begin
SELECT distinct n.编号
,n.公司名称
,n.姓名
,n. 证件类型
,dbo.F_Iden15To18(n.证件号) --转换18位
,n.更新时间
,o. 编号
,o.公司名称
,o.姓名
,o.证件类型
,o. 证件号
,o.更新时间
FROM 新表 AS n,(SELECT *,ROW_NUMBER()OVER(PARTITION BY o.证件号 ORDER BY o.更新时间) AS RN FROM o.旧表 WHERE 证件类型= '身份证') AS o
where LEN(n.证件号) = 15 and o.RN = 1
and n.公司名称 = o.公司名称
and n.姓名 = o.姓名
and n.证件类型 = '身份证'
and o.证件类型 = '身份证'
and n.证件号 <> o.证件号
order by n.更新时间 desc
update
新表
SET n.证件号=dbo.F_Iden15To18(n.证件号) WHERE LEN(n.证件号) = 15 AND n.证件类型= '身份证'--将15位身份证升级成18位的用户定义函数
use tempdb
go
if object_id('F_Iden15To18') is not null
drop function F_Iden15To18
go
create function F_Iden15To18(@Iden char(15))
returns char(18)
as
begin
if len(@Iden)<>15
return @Iden
declare @CID varchar(18),@i int,@sum int
select @CID=left(@Iden,6) + '1'+case when right(@Iden,3) in('999','998','997','996') then '8' else '9' end + right
(@Iden,9),@i=1,@sum=0
while @i<=17
select @sum=substring(@CID,18-@i,1)*(power(2,@i)%11)+@sum,@i=@i+1
return (@CID+case 12-@sum%11 when 10 then 'X' else rtrim(12-@sum%11) end)
end
go
update 新表 t0
set t0.证件号=ID15TO18(t0.证件号)
from 旧表 t1
where t0.姓名 = t1.姓名 and t0.公司 = t1.公司
and t0.年份=t1.年份
and t0.证件类型 = '身份证' and t1.证件类型= '身份证' and len(t1.证件号)=18