-------SQL 2005 的问题哟....技术不达标，请大家帮忙！-----

[看尽繁华亦微笑]

我有个表 tb：

姓名 性别 邮编 区域 身份证 年龄

何光 NULL NULL NULL 310228600912221 NULL
刘月 女 201613 松江 310106195403162428 NULL
褚晓 NULL 201202 浦东新区 284771578 NULL
任广 NULL 200042 静安 31022419760414562X 33
刘静 男 200023 卢湾 310226661223491 NULL
都锡 男 NULL NULL 310228800912231 NULL
周建 NULL 201112 闵行 310228194701013815 NULL


需要根据身份证把tb表性别和年龄添进去
更改为：

姓名 性别 邮编 区域 身份证 年龄

何光 女 NULL NULL 310022860912221 23
刘月 女 201613 松江 310106198403162428 25
褚晓 NULL 201202 浦东新区 284771578 null
任广 女 200042 静安 31022419760414562X 33
刘静 男 200023 卢湾 310212866223491 23
都锡 男 NULL NULL 310922880012231 21
周建 男 201112 闵行 310228198701013815 22

语句怎么写呀，谢谢大家！
（18位身份证第七位至十四位表示编码对象出生的年、月、日， 15位的78位是年
倒数第2位奇数是 男，偶数是 女）

[看尽繁华亦微笑]

希望大家帮忙！
3口！

[--小F--]

力气活 先MARK

[--小F--]

15位的怎么办 还要转换为18位的

[快乐_石头]

284771578 身分證
太短了吧?
分15位與18位
去處理~~

[看尽繁华亦微笑]

恩，数据太乱了 身份证里有些还是null 有些还是中文....
哎。老板最先弄的表。。他都不懂。。。

[--小F--]

update
  tb
set
  性别=(case when left(right(身份证,2),1)%2=1 then '男' else '女' end)

[看尽繁华亦微笑]

那怎么15位和18位的身份证筛选出来另生成一列呢？

然后在转换？

[gxjwqm]

310022860912221??
284771578 ??

[dawugui]

create table tb(身份证 varchar(18),                   年龄 int)
insert into tb values('310228600912221'   ,       NULL )
insert into tb values('310106195403162428',        NULL) 
insert into tb values('284771578'         ,       NULL )
insert into tb values('31022419760414562X',        33 )
insert into tb values('310226661223491'   ,       NULL )
insert into tb values('310228800912231'   ,       NULL )
insert into tb values('310228194701013815',        NULL )
go

--查询
select 身份证 , 
       年龄 = case when len(身份证) = 18 then year(getdate()) - cast(substring(身份证,7,4) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) <= '99' then year(getdate()) - cast('19'+substring(身份证,7,2) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) >= '00' then year(getdate()) - cast('20'+substring(身份证,7,2) as int) 
              end
from tb
/*
身份证                年龄          
------------------ ----------- 
310228600912221    49
310106195403162428 55
284771578          NULL
31022419760414562X 33
310226661223491    43
310228800912231    29
310228194701013815 62

（所影响的行数为 7 行）
*/

--更新
update tb set
       年龄 = case when len(身份证) = 18 then year(getdate()) - cast(substring(身份证,7,4) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) <= '99' then year(getdate()) - cast('19'+substring(身份证,7,2) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) >= '00' then year(getdate()) - cast('20'+substring(身份证,7,2) as int) 
              end
from tb
select * from tb
/*
身份证                年龄          
------------------ ----------- 
310228600912221    49
310106195403162428 55
284771578          NULL
31022419760414562X 33
310226661223491    43
310228800912231    29
310228194701013815 62

（所影响的行数为 7 行）
*/

drop table tb

[dawugui]

--更改一下我九楼的语句,我觉得是否应该还要考虑月和日的问题,如果只考虑年,则用九楼的即可,否则如下:

create table tb(身份证 varchar(18),                   年龄 int)
insert into tb values('310228600912221'   ,       NULL )
insert into tb values('310106195403162428',        NULL) 
insert into tb values('284771578'         ,       NULL )
insert into tb values('31022419760414562X',        33 )
insert into tb values('310226661223491'   ,       NULL )
insert into tb values('310228800912231'   ,       NULL )
insert into tb values('310228194701013815',        NULL )
go

--查询
select 身份证 , 
       年龄 = case when len(身份证) = 18 and substring(身份证,11,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) 
                   when len(身份证) = 18 and substring(身份证,11,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) - 1
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) <= '99' and substring(身份证,9,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('19'+substring(身份证,7,2) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) >= '00' and substring(身份证,9,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('20'+substring(身份证,7,2) as int) - 1
              end
from tb
/*
身份证                年龄          
------------------ ----------- 
310228600912221    49
310106195403162428 55
284771578          NULL
31022419760414562X 33
310226661223491    43
310228800912231    29
310228194701013815 62

（所影响的行数为 7 行）
*/

--更新
update tb set
       年龄 = case when len(身份证) = 18 and substring(身份证,11,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) 
                   when len(身份证) = 18 and substring(身份证,11,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) - 1
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) <= '99' and substring(身份证,9,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('19'+substring(身份证,7,2) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) >= '00' and substring(身份证,9,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('20'+substring(身份证,7,2) as int) - 1
              end
from tb
select * from tb

/*
身份证                年龄          
------------------ ----------- 
310228600912221    49
310106195403162428 55
284771578          NULL
31022419760414562X 33
310226661223491    43
310228800912231    29
310228194701013815 62

（所影响的行数为 7 行）
*/

drop table tb

[--小F--]

--年龄的
update
  tb
set
  年龄=(case when len(身份证)=15 then datediff(yy,convert(varchar(10),'19'+cast(substring(身份证,7,6) as varchar(10),120),getdate())
            when len(身份证)=15 then datediff(yy,convert(varchar(10),ltrim(substring(身份证,7,8)),getdate())
end

[看尽繁华亦微笑]

恩 谢谢
年龄可以的哎·
性别能转化么

[--小F--]

[Quote=引用 12 楼 storylove 的回复:]
恩  谢谢
年龄可以的哎·
性别能转化么
[/Quote]
6楼

[dawugui]

[Quote=引用 12 楼 storylove 的回复:]
恩  谢谢
年龄可以的哎·
性别能转化么
[/Quote]
可以,性别为倒数第二位和2取余,单数(1)为男,双数(0)为女

create table tb(身份证 varchar(18),                   年龄 int,性别 varchar(2))
insert into tb values('310228600912221'   ,       NULL ,NULL)
insert into tb values('310106195403162428',        NULL,NULL) 
insert into tb values('31022419760414562X',        33 ,NULL)
insert into tb values('310226661223491'   ,       NULL ,NULL)
insert into tb values('310228800912231'   ,       NULL ,NULL)
insert into tb values('310228194701013815',        NULL ,NULL)
go

--查询
select 身份证 , 
       年龄 = case when len(身份证) = 18 and substring(身份证,11,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) 
                   when len(身份证) = 18 and substring(身份证,11,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) - 1
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) <= '99' and substring(身份证,9,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('19'+substring(身份证,7,2) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) >= '00' and substring(身份证,9,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('20'+substring(身份证,7,2) as int) - 1
              end,
       性别 = (case when cast(substring(reverse(身份证),2,1) as int)%2 = 1 then '男' else '女' end)
from tb
/*
身份证                年龄          性别   
------------------ ----------- ---- 
310228600912221    49          女
310106195403162428 55          女
31022419760414562X 33          女
310226661223491    43          男
310228800912231    29          男
310228194701013815 62          男

（所影响的行数为 6 行）

*/

--更新
update tb set
       年龄 = case when len(身份证) = 18 and substring(身份证,11,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) 
                   when len(身份证) = 18 and substring(身份证,11,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast(substring(身份证,7,4) as int) - 1
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) <= '99' and substring(身份证,9,4) <= right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('19'+substring(身份证,7,2) as int) 
                   when len(身份证) = 15 and cast(substring(身份证,7,2) as int) >= '00' and substring(身份证,9,4) > right(convert(varchar(8),getdate(),112),4) then year(getdate()) - cast('20'+substring(身份证,7,2) as int) - 1
              end,
       性别 = (case when cast(substring(reverse(身份证),2,1) as int)%2 = 1 then '男' else '女' end)
from tb
select * from tb

/*
身份证                年龄          性别   
------------------ ----------- ---- 
310228600912221    49          女
310106195403162428 55          女
31022419760414562X 33          女
310226661223491    43          男
310228800912231    29          男
310228194701013815 62          男

（所影响的行数为 6 行）
*/

drop table tb

[看尽繁华亦微笑]

[Quote=引用 13 楼 fredrickhu 的回复:]
引用 12 楼 storylove 的回复:
恩  谢谢
年龄可以的哎·
性别能转化么

6楼
[/Quote]

3口。高手啊都是，。这么快 呵呵

[看尽繁华亦微笑]

(小F) (爱新觉罗.毓华) 一个50分 吧！~怎么样~ 呵呵 谢谢你们2个！

[看尽繁华亦微笑]

[Quote=引用 4 楼 happy_stone 的回复:]
284771578 身分證
太短了吧?
分15位與18位
去處理~~
[/Quote]

另谢谢你的建议！