Excel 2010 SQL应用041 根据二代身份证号返回出生年份

源数据表

序号	姓名	身份证号
1	黄有国	4526221968****1654
2	陈燕妮	4508811984****7447
3	张海波	4501051977****0019
4	王福英	4503211990****4521
5	朱志南	4503211984****1010
6	苏雯雯	4526271980****0062
7	谭宝树	4509811989****3533
8	欧夏丽	4521281994****4026
9	吴伟	3623221988****043X
10	巫宝兵	4501211987****4834
11	黄丽萍	4526011992****3025
12	黄耋仙	4526011975****3617
13	吴华	4528261974****1117
14	陈纯纯	4505021981****0320
15	农先利	4521311980****3036
16	麦清霞	4508021986****8960
17	黄永群	4506811969****2423
18	缪艳	4500001965****1323
19	苏鹏飞	4506021988****4818
20	黄本佳	4525281983****7019
21	黄宏翔	4528221968****0034
22	钟雨玲	4506031989****5726
23	李禧明	4508021988****2550
24	黄桂新	4503041973****0516
25	徐颖	4522311979****3531
26	王静	4503041987****2521
27	韦永荣	4527011975****0050
28	宋永明	4506031993****3613
29	盘金	4522261981****4217
30	刘义践	4305211977****3791
31	黄田奋	4522251963****2110
32	陈玲玲	4522241985****0525
33	莫用武	4522241989****1535
34	苏雪燕	4522281985****2029
35	曾孟彪	4502221989****0351
36	欧经翘	4522261986****3335
37	张振兴	4507021987****8436
38	卢显军	4524261982****2136
39	毛敬华	3623221988****2755
40	朱宝民	4501111986****2737
41	邓素宁	4501041958****202X
42	刘崇良	4525251969****4411
43	黄金红	4526241988****1368
44	韦华春	4521231984****1324
45	梁锦湖	4508811986****6535
46	练文娟	4522301986****602X
47	高瑞焕	4524211962****0044
48	彭孔妮	4504221986****282X
49	黎耀东	4509211989****241X
50	罗国信	4525011972****1211
51	刘安宜	4509211994****4429
52	黄绍超	4509811987****2076
53	夏静	4509211989****4427
54	农锋	4526221983****0854
55	廖立	4526231967****0311
56	黄英格	4526261965****001X
57	詹喜	4505211983****2513
58	庞剑	4525281964****781X
59	冯道远	4505211980****7838
60	张伟安	4505031983****0610
61	张军	4501211986****0078
62	苏满芳	4506021989****4836
63	陈泰宇	4506031978****0018
64	胡幻	4506031986****002X
65	张冲	4508021983****8214
66	陆亮秋	4508021979****4335
67	李活	4508211985****3073
68	廖浩名	4508811988****0611
69	陆年和	4525231980****9010
70	韦敏丽	4527301989****5324
71	黄爱英	4523301949****0024
72	翟齐湘	4503271989****1228
73	周萍	4503231992****1228
74	刘传明	4503221990****5018
75	唐骋炜	4503021989****1038
76	覃金仙	4521271987****2761
77	银小智	4527231984****0058
78	卢永朋	4524261983****0912
79	陈金红	4522261985****0646
80	苏家明	4522301988****0015
81	黄育淇	4522251978****0019
82	黄倬	4522241991****2538
83	王平	4304031984****2037
84	梁旋权	4522251978****3916
85	何崇杰	3501811987****1832
86	罗建红	4502221984****0628
87	唐培晴	4503241989****1694
88	何春瑜	4502051985****0425
89	韦承详	4522261979****6958
90	黎崇德	4506211990****1715
91	李乃鹏	4509231990****4377
92	余敏秋	4508811987****180X
93	韦娟娟	4522291988****6129
94	谢华琛	4508021988****9655
95	赵勇生	4502221988****1350
96	李兆阳	4502211965****0012
97	马翀	4502111986****031X
98	练祖春	4507211989****0417
99	余凤	4528261971****1524
100	叶干	4507221985****0412
101	周其宇	4501211988****0615
102	吴础明	4508211983****1656
103	李承想	4501211979****1510
104	何明	4208021984****0610
105	赵荣彪	4521301978****1218
106	黄泽邕	4501211979****1213
107	刘影	4503031985****0034
108	陆正世	4501061943****0518
109	黄钰婷	4501061984****1020
110	严世富	4523241981****1533
111	刘新明	4211261968****7155
112	钟正业	4502221977****0334
113	曾新云	4523231981****2210
114	韦林颜	4527311983****6028
115	黄有薄	4501211988****2453
116	李炜	4304041968****1030
117	梁洁	4509241987****3922
118	管志新	4501041978****0018
119	钟世旺	4521221971****0354
120	黄海航	4506211989****1755
121	韦春玲	4521231986****584X
122	章艳	3425301985****1621
123	王林	4113021988****451X
124	凌鑫	4521311986****3032
125	兰凤萍	4521261987****2722
126	刘升海	4522271983****5013
127	赵付萍	5223211989****2521
128	俸榕萍	4503021987****2064
129	黄启辉	4526241979****3354
130	李绍联	4521221975****0459
131	陈彦	4502051982****0427
132	韦智敏	4501031982****2535
133	黄奕汉	4528241956****0012
134	陈雄波	4406241970****5752
135	邱宗秋	4524241984****0315
136	聂桂兰	4504211980****0521
137	莫炎玲	4524231981****0024
138	彭昌成	4504031986****2410
139	唐嘉杰	4504031985****1518
140	邵运媚	4504211992****5564
141	覃爽	4509211990****0010
142	庞锡林	4525011954****0913
序号	姓名	身份证号
1	黄有国	4526221968****1654
2	陈燕妮	4508811984****7447
3	张海波	4501051977****0019
4	王福英	4503211990****4521
5	朱志南	4503211984****1010
6	苏雯雯	4526271980****0062
7	谭宝树	4509811989****3533
8	欧夏丽	4521281994****4026
9	吴伟	3623221988****043X
10	巫宝兵	4501211987****4834
11	黄丽萍	4526011992****3025
12	黄耋仙	4526011975****3617
13	吴华	4528261974****1117
14	陈纯纯	4505021981****0320
15	农先利	4521311980****3036
16	麦清霞	4508021986****8960
17	黄永群	4506811969****2423
18	缪艳	4500001965****1323
19	苏鹏飞	4506021988****4818
20	黄本佳	4525281983****7019
21	黄宏翔	4528221968****0034
22	钟雨玲	4506031989****5726
23	李禧明	4508021988****2550
24	黄桂新	4503041973****0516
25	徐颖	4522311979****3531
26	王静	4503041987****2521
27	韦永荣	4527011975****0050
28	宋永明	4506031993****3613
29	盘金	4522261981****4217
30	刘义践	4305211977****3791
31	黄田奋	4522251963****2110
32	陈玲玲	4522241985****0525
33	莫用武	4522241989****1535
34	苏雪燕	4522281985****2029
35	曾孟彪	4502221989****0351
36	欧经翘	4522261986****3335
37	张振兴	4507021987****8436
38	卢显军	4524261982****2136
39	毛敬华	3623221988****2755
40	朱宝民	4501111986****2737
41	邓素宁	4501041958****202X
42	刘崇良	4525251969****4411
43	黄金红	4526241988****1368
44	韦华春	4521231984****1324
45	梁锦湖	4508811986****6535
46	练文娟	4522301986****602X
47	高瑞焕	4524211962****0044
48	彭孔妮	4504221986****282X
49	黎耀东	4509211989****241X
50	罗国信	4525011972****1211
51	刘安宜	4509211994****4429
52	黄绍超	4509811987****2076
53	夏静	4509211989****4427
54	农锋	4526221983****0854
55	廖立	4526231967****0311
56	黄英格	4526261965****001X
57	詹喜	4505211983****2513
58	庞剑	4525281964****781X
59	冯道远	4505211980****7838
60	张伟安	4505031983****0610
61	张军	4501211986****0078
62	苏满芳	4506021989****4836
63	陈泰宇	4506031978****0018
64	胡幻	4506031986****002X
65	张冲	4508021983****8214
66	陆亮秋	4508021979****4335
67	李活	4508211985****3073
68	廖浩名	4508811988****0611
69	陆年和	4525231980****9010
70	韦敏丽	4527301989****5324
71	黄爱英	4523301949****0024
72	翟齐湘	4503271989****1228
73	周萍	4503231992****1228
74	刘传明	4503221990****5018
75	唐骋炜	4503021989****1038
76	覃金仙	4521271987****2761
77	银小智	4527231984****0058
78	卢永朋	4524261983****0912
79	陈金红	4522261985****0646
80	苏家明	4522301988****0015
81	黄育淇	4522251978****0019
82	黄倬	4522241991****2538
83	王平	4304031984****2037
84	梁旋权	4522251978****3916
85	何崇杰	3501811987****1832
86	罗建红	4502221984****0628
87	唐培晴	4503241989****1694
88	何春瑜	4502051985****0425
89	韦承详	4522261979****6958
90	黎崇德	4506211990****1715
91	李乃鹏	4509231990****4377
92	余敏秋	4508811987****180X
93	韦娟娟	4522291988****6129
94	谢华琛	4508021988****9655
95	赵勇生	4502221988****1350
96	李兆阳	4502211965****0012
97	马翀	4502111986****031X
98	练祖春	4507211989****0417
99	余凤	4528261971****1524
100	叶干	4507221985****0412
101	周其宇	4501211988****0615
102	吴础明	4508211983****1656
103	李承想	4501211979****1510
104	何明	4208021984****0610
105	赵荣彪	4521301978****1218
106	黄泽邕	4501211979****1213
107	刘影	4503031985****0034
108	陆正世	4501061943****0518
109	黄钰婷	4501061984****1020
110	严世富	4523241981****1533
111	刘新明	4211261968****7155
112	钟正业	4502221977****0334
113	曾新云	4523231981****2210
114	韦林颜	4527311983****6028
115	黄有薄	4501211988****2453
116	李炜	4304041968****1030
117	梁洁	4509241987****3922
118	管志新	4501041978****0018
119	钟世旺	4521221971****0354
120	黄海航	4506211989****1755
121	韦春玲	4521231986****584X
122	章艳	3425301985****1621
123	王林	4113021988****451X
124	凌鑫	4521311986****3032
125	兰凤萍	4521261987****2722
126	刘升海	4522271983****5013
127	赵付萍	5223211989****2521
128	俸榕萍	4503021987****2064
129	黄启辉	4526241979****3354
130	李绍联	4521221975****0459
131	陈彦	4502051982****0427
132	韦智敏	4501031982****2535
133	黄奕汉	4528241956****0012
134	陈雄波	4406241970****5752
135	邱宗秋	4524241984****0315
136	聂桂兰	4504211980****0521
137	莫炎玲	4524231981****0024
138	彭昌成	4504031986****2410
139	唐嘉杰	4504031985****1518
140	邵运媚	4504211992****5564
141	覃爽	4509211990****0010
142	庞锡林	4525011954****0913

    员工档案中登记了员工的身份证号码，如图所示，身份证号码已经整理并列于“身份证信息”表  中。从这些二代身份证号码中提取出生年份以便进一步分析。

解决方案

通过MID函数截取身份证号码中指定位置指定长度的字符串获得出生年份。

SELECT 姓名,MID(身份证号，7，4) AS 出生年份
FROM [sheet1$]

    身份证号码中隐藏了许多信息。二代身份证号码的长度为18位，前6位为地区信息，第7~14位为出生年月日，第15～17位为顺序号，第18位为校验码。
    身份证号码的第7~14位为出生年月日，其中第7～10位为出生年份。使用MID函数从身份证号码的第7位开始截取4位，公式如下：  

MID(身份证号,7,4)

    MID函数是用来截取字符串中的部分字符串的函数，其语法如下：  

MID(string,start[,length])

    其中，string表示待截取的字符串，start表示截取字符串的起始位置，length为截取字符串的长度。其中第二个参数start大于0，第三个参数length大于或等于0。如果省略第三个参数，
则返回自start参数起的所有字符。
    当MID函数的第二个参数start及第三个参数length为小数时，按照四舍六入五单双的进位规则取整，然后返回对应字符串。

       MID函数第二、三参数为小数时的返回结果说明

序号	输入公式	相当于公式	说  明
1	MID(ISBN,0.4,0.4)	MID(ISBN,0，0)	因为函数的第二个参数要求大于0，因此该语句将提示错误。
2	MID(ISBN,1.4,O.5)	MID(ISBN,1,0)	返回ISBN字段中从第一个字符开始的零长度字符串。
3	MID(ISBN,1.5,1.4)	MID(ISBN,2,1)	返回ISBN字段中从二个字符开始的1个字符长度的字符串。
4	MID(ISBN,2.5,1.5)	MID(ISBN,2,2)	返回ISBN字段中从第二个字符开始的2个字符长度的字符串。
5	MID(ISBN,2.5,2.5)	MID(ISBN,2,2)	返回ISBN字段中从第二个字符开始的2个字符长度的字符串。
6	MID(ISBN,2.6,2.6)	MID(ISBN,3,3)	返回ISBN字段中从第三个字符开始的3个字符长度的字符串
7	MID(ISBN,3) MID(ISBN,3,)	MID(ISBN,3,LEN(ISBN)-2)	返回lSBN宇段第三个字符开始的所有字符
8	MID(ISBN,m,n)  (m小于ISBN字段的长度，且n大于ISBN字段的长度)	MID(ISBN,m,LEN(ISBN)-n+1)	返回ISBN字段第m个字符之后的所有字符
9	MID(ISBN,m，n) (m大于ISBN字段的长度．n小于ISBN字段的长度）	MIO(ISBN,m,0)	返回零长度字符串
10	MID(ISBN,m,n) （m大于ISBN字段的长度，n大于ISBN字段的长度）	MID(ISBN,m,0)	返回零长度字符串

MID函数除了可以截取文本类型的数据外，也可以截取其他类型的数据。在截取其他类型的数据时，Excel会自动将其他类型的数据转换为文本格式进行计算和操作。

四舍六入五单双的规则

    四舍六入五单双的规则如下：
    1．当需舍弃部分最左一位数字不等于5时，遵循普通的四舍五入的进位规则，如1.492保留两位小数为1.49，保留一位小数为1.5。
    2．当需舍弃部分最左一位数字为5时，如果5右边的有效数字非零，则进一位。如1.452保留一位小数，则进位为1.5。如果5右边的有效数字为零，则要判断5前面数字的奇偶性，
如果为奇数，则进一位；如果为偶数，则舍去。如1.55保留两位有效数字为1.6，1.45保留两位有效数字为1.4。
    为方便记忆，归纳口诀如下：
    四舍六入五考虑，五后非零就进一，五后皆零看奇偶，五前为偶应舍去，五前为奇要进一。

MIDB函数的用法

    MIDB函数与MID函数一样，都是从字符串中提取部分字符串。二者的区别在于：MID按照字符长度提取字符串，而MIDB按照字节长度提取字符串。

SELECT 规格型号，
MID (规格型号，3,2) AS S1，
MIDB（规格型号,3,2）AS S2
FROM [sheet1$]

    其中，MID(规格型号,3,2)返回规格型号字段中从第三个字符开始的2个字符长度的字符  串，而MIDB(规格型号,．3,2)返回规格型号字段中从第三个字节开始的2个字节长度的字符串。

字符与字节的关系

    在SQL中，字节与字符的关系与在Excel工作表中是不同的。Excel工作表中半角英文字母为单字符单字节，而在SQL中为单字符双字节。在工作表中输入如下公式后，其返回结果均为“B2”。

公式1:=MID(A2,3,2)
公式2:=MIDB(A2,3,2)