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

Question

我有个表 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位奇数是 男，偶数是 女）

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

--年龄的
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

Answer 6

可以,性别为倒数第二位和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