Shulou(Shulou.com)06/01 Report--

What the editor wants to share with you this time is how Oracle can get provinces and cities, gender and age by ID card number. The article is rich in content. Interested friends can learn about it. I hope you can get something after reading this article.

1. Inquire the province and city through the ID card number.

SELECTcount (*) as total, case substr (t.CERTNO When '11' then' Beijing 'when' 12' then 'Tianjin' when '13' then' Hebei Province 'when' 14' then 'Shanxi Province' when '15' then' Inner Mongolia Autonomous region 'when' 21' then 'Liaoning Province' when '22' then' Jilin Province 'when' 23' then 'Heilongjiang Province' when '31' then' Shanghai 'when' 32' then 'Jiangsu Province' when 33 " 'then' Zhejiang Province 'when' 34' then 'Anhui Province' when '35' then' Fujian Province 'when' 36' then 'Jiangxi Province' when '37' then' Shandong Province 'when' 41' then 'Henan Province' when '42' then' Hubei Province 'when' 43' then 'Hunan Province' when '44' then' Guangdong Province 'when' 45' then 'Guangxi Zhuang Autonomous region' when '46' then' Hainan Province When '50' then' Chongqing 'when' 51' then 'Sichuan Province' when '52' then' Guizhou Province 'when' 53' then 'Yunnan Province' when '54' then' Xizang Autonomous region 'when' 61' then 'Shaanxi Province' when '62' then' Gansu Province 'when' 63' then 'Qinghai Province' when '64' then' Ningxia Hui Autonomous region 'when' 65' then 'Xinjiang Uygur Autonomous region District 'when' 71 'then' Taiwan Province 'when' 81 'then' Hong Kong Special Administrative region 'when' 82 'then' Macao Special Administrative region 'else' unknown 'end AS province FROM uip_bjt_userinfo t group by case substr (t.CERTNO) When '11' then' Beijing 'when' 12' then 'Tianjin' when '13' then' Hebei 'when' 14' then 'Shanxi Province' when '15' then' Inner Mongolia Autonomous region 'when' 21' then 'Liaoning Province' when '22' then' Jilin Province 'when' 23' then 'Heilongjiang Province' when '31' then' Shanghai 'when' 32' Then 'Jiangsu' when '33' then' Zhejiang 'when' 34' then 'Anhui' when '35' then' Fujian 'when' 36' then 'Jiangxi Province' when '37' then' Shandong Province 'when' 41' then 'Henan Province' when '42' then' Hubei Province 'when' 43' then 'Lake Province' when '44' then' Guangdong Province 'when' 45' Then 'Guangxi Zhuang Autonomous region' when '46' then' Hainan Province 'when' 50' then 'Chongqing' when '51' then' Sichuan Province 'when' 52' then 'Guizhou Province' when '53' then' Yunnan Province 'when' 54' then 'Xizang Autonomous region' when '61' then' Shaanxi Province 'when' 62' then 'Gansu Province' when '63' then' Qinghai Province " When '64' then' Ningxia Hui Autonomous region 'when' 65' then 'Xinjiang Uygur Autonomous region' when '71' then' Taiwan Province 'when' 81' then 'Hong Kong Special Administrative region' when'82 'then' Macao Special Administrative region 'else' unknown 'end order by province desc

2. Get the gender through the ID card number (odd number is male, even number is female)

Select decode (mod (to_number (substr (t.useridcardnum, 17,1)), 2), 0 as sex from uip_ca_userinfo 'female', 'male')

3. Get the age through the ID card number

Select to_char (sysdate, 'yyyy')-substr (t.useridcardnum, 7,4) as age from uip_ca_userinfo t

4. count the number of people in the age group through the ID card number.

Select count (t.id), case when to_char (sysdate, 'yyyy')-substr (t.useridcardnum, 7,4) between 1 and 20 then' 1-20 'when to_char (sysdate,' yyyy')-substr (t.useridcardnum, 7,4) between 21 and 30 then'21-30 'when to_char (sysdate,' yyyy')-substr (t.useridcardnum, 7,4) between 31 and 40 then'31-40 'when to_char (sysdate 'yyyy')-substr (t.useridcardnum, 7,4) between 41 and 50 then' 41-50 years old 'else' over 50 years old 'end as age from uip_ca_userinfo t group by case when to_char (sysdate,' yyyy')-substr (t.useridcardnum, 7,4) between 1 and 20 then'1-20 years old 'when to_char (sysdate,' yyyy')-substr (t.useridcardnum, 7 4) between 21 and 30 then'21-30 'when to_char (sysdate,' yyyy')-substr (t.useridcardnum, 7,4) between 31 and 40 then'31-40 'when to_char (sysdate,' yyyy')-substr (t.useridcardnum, 7,4) between 41 and 50 then'41-50 else over 50 'end order by age asc

5. Count the number of men and women through the ID card number

Select count (t.id), decode (mod (to_number (substr (t.useridcardnum, 17,1), 2), 0select count 'female', 'male') as sex from uip_ca_userinfo t where to_char (sysdate, 'yyyy')-substr (t.useridcardnum, 7,4) between 1 and 26 group by decode (mod (to_number (substr (t.useridcardnum, 17,1), 2), 0' female', 'male')

After reading this article about how Oracle can get province, city, gender and age by ID number, if you think the content of the article is good, you can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.