SQL进阶:数据中间表,多表取身份证号-整理-匹配多表-合并整理

数据中间表
定义：需要反复比对的数据，通过查找其业务之间彼此联系，然后通过身份证将多数据合并
常见中间表：非贫困人口，财政供养人员，死亡人口
提高公共数据利用率，缩短查询时间，减少冗余，方便查询

步骤一:获取几个表中的身份证号并将之汇总在非贫困人口_步骤一表中

DROP TABLE [非贫困人口_步骤1]  /*若有,则删除该表*/
GO
SELECT DISTINCT [身份证明号码] '身份证号码' INTO [非贫困人口_步骤1] FROM [车辆数据标准表]
UNION                        /*将查询数据连接,去重;UNION ALL为不去重*/
SELECT DISTINCT [身份证号码] '身份证号码'  FROM [财政供养人员信息表]
UNION
SELECT DISTINCT [身份证号或组织机构代码证号] '身份证号码' FROM 农机购置补贴发放表
UNION 
SELECT DISTINCT [法人证件号码] '身份证号码' FROM  [工商登记信息表]
UNION
SELECT DISTINCT [持证人或企业法人身份证号] '身份证号码' FROM  [湖北水产养殖许可证登记表]
UNION
SELECT DISTINCT [身份证号] '身份证号码' FROM  [机关事业保险人员信息表_含退休]
GO

步骤 二,处理身份证号,使其标准化

--2、剔除非标准身份证，提取15位与18位身份证号码
DROP TABLE [非贫困人口_步骤2]
GO
SELECT [身份证号码]
INTO [非贫困人口_步骤2]
FROM [非贫困人口_步骤1]
WHERE  [身份证号码] not like '0%' AND [身份证号码] not like '%0000' AND [身份证号码] not like '%11111111%' AND [身份证号码] IS NOT NULL AND 
  (
	(LEN([身份证号码])=15 AND LEFT([身份证号码],15) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') OR 
	(LEN([身份证号码])=18 AND LEFT([身份证号码],17) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
  )
GO

步骤三,通过标准化的身份证号查询各表中的数据

--3、将身份证号与行业数据关联。
DROP TABLE [非贫困人口_步骤3]
GO
SELECT DISTINCT A.[身份证号码],'|' [1、车辆],B.[号牌种类],B.[号牌号码],B.[管理部门],B.[所有人],B.[身份证明号码],B.[车辆品牌],B.[品牌明细],B.[车辆类型],B.[车辆型号],B.[使用性质],B.[初次登记时间],B.[检验有效期止],B.[核定载客],B.[核定载质量],
    '|' [2、公职人员],C.[职务（职称）],C.[人员身份],C.[单位名称],C.[参加工作时间],
    '|' [3、农机购置],D.[设备数量],D.[机具编号],D.[机具大类],D.[机具小类],D.[机具品目],D.[分档名称],D.[机具型号],D.[生产企业名称],D.[经销商名称],D.[结算单位],D.[数量],D.[最终销售总价],D.[总补贴额],D.[年度],D.[购机日期],D.[出厂编号发动机号],
    '|' [4、企业法人],E.[企业名称],E.[工商营业执照号码],E.[类型名称],E.[住所],E.[成立时间],E.[核准日期],E.[登记机关名称],E.[行业类型名称],E.[注册资本],E.[实收资本],E.[经营范围],E.[营业期限有效期起],E.[营业期限有效期止],
    '|' [6、水产养殖],G.[水域滩涂养殖证编号],G.[发证账号],G.[发证部门],G.[数据录入时间],G.[申请日期],G.[申请类型],G.[申请人类型],G.[持证人名称],G.[企业法人],G.[所有制性质],G.[水域滩涂类型],G.[养殖方式],G.[水域滩涂面积],G.[有效期开始日期],G.[有效期结束日期],G.[发证机关],G.[水域滩涂登记编号],G.[图号],G.[签发日期],G.[是否已注销],G.[注销日期],G.[注销原因],G.[备注],
    '|' [8、机关事业保险],I.[人员编号],I.[姓名] [机保_姓名],I.[性别] [机保_性别],I.[民族],I.[出生日期] [机保_出生日期],I.[参加工作时间] as 退休参加工作时间,I.[证件类型],I.[身份证号],I.[退休时间],I.[离退休标识],I.[户口性质],I.[家庭住址],I.[学历],I.[婚姻状况],I.[联系电话],I.[编制类型],I.[单位名称] [机保_单位名称],I.[行政区划代码],I.[身份岗位],I.[事业专业技术岗位]
INTO [非贫困人口_步骤3]
FROM [非贫困人口_步骤2] A LEFT JOIN [车辆数据标准表] B
ON A.身份证号码=B.身份证明号码 AND B.[车辆类型] NOT LIKE '%摩托车%' AND B.[车辆类型] NOT IN('手扶拖拉机','小型轮式拖拉机') AND B.[号牌种类] NOT IN('低速车','教练摩托车','教练汽车','警用摩托','警用汽车','普通摩托车','轻便摩托车','试验汽车','拖拉机','原农机号牌')
LEFT JOIN [财政供养人员信息表] C
ON A.身份证号码=C.身份证号码
LEFT JOIN 农机购置补贴发放表 D
ON A.身份证号码=D.[身份证号或组织机构代码证号] AND CAST(D.[最终销售总价] AS FLOAT)>=50000
LEFT JOIN [工商登记信息表] E
ON A.身份证号码=E.法人证件号码
LEFT JOIN [湖北水产养殖许可证登记表] G
ON A.身份证号码=G.[持证人或企业法人身份证号] AND G. [养殖方式]<>'网箱'
LEFT JOIN [机关事业保险人员信息表_含退休] I
ON A.身份证号码=I.身份证号
GO