Autobio_API/sql/sql优化数...

----优化数据 - 处理省份错乱问题，跟列电弹屏有关（也修改） a.Address = b.F_CustomerIndustry 来电单位  distinct b.F_Province
--select b.F_Province,a.CreateUserID,* from [dbo].[T_Wo_WorkOrder] a,T_Cus_CustomerBase b 
--where a.Address = b.F_CustomerIndustry and b.F_Province is not NULL
--	AND b.F_Province = '短期支持'

--备份
SELECT * INTO T_Cus_CustomerBase_20180907 FROM dbo.T_Cus_CustomerBase
SELECT * INTO T_Wo_WorkOrder_20180907 FROM dbo.T_Wo_WorkOrder

-- 短期支持,热线办公室,无
----------------------------------------------------
--短期支持,热线办公室,无
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '短期支持')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = ''	WHERE F_Province = '短期支持'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = ''	WHERE CreateUserID = '短期支持'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '热线办公室')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = ''	WHERE F_Province = '热线办公室'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = ''	WHERE CreateUserID = '热线办公室'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '无')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = ''	WHERE F_Province = '无'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = ''	WHERE CreateUserID = '无'
END
----------------------------------------------------
--北京市
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '北京')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '北京市'	WHERE F_Province = '北京'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '北京市'	WHERE CreateUserID = '北京'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '背景')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '北京市'	WHERE F_Province = '背景'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '北京市'	WHERE CreateUserID = '背景'
END
----------------------------------------------------
--安徽省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '安徽')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '安徽省'	WHERE F_Province = '安徽'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '安徽省'	WHERE CreateUserID = '安徽'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '安微省')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '安徽省'	WHERE F_Province = '安微省'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '安徽省'	WHERE CreateUserID = '安微省'
END
----------------------------------------------------
--甘肃省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '甘肃')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '甘肃省'	WHERE F_Province = '甘肃'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '甘肃省'	WHERE CreateUserID = '甘肃'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '甘宁青')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '甘肃省'	WHERE F_Province = '甘宁青'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '甘肃省'	WHERE CreateUserID = '甘宁青'
END
----------------------------------------------------
--广东省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '广东')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '广东省'	WHERE F_Province = '广东'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '广东省'	WHERE CreateUserID = '广东省'
END
----------------------------------------------------
--广西自治区
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '广西')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '广西自治区'	WHERE F_Province = '广西'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '广西自治区'	WHERE CreateUserID = '广西'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '广西省')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '广西自治区'	WHERE F_Province = '广西省'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '广西自治区'	WHERE CreateUserID = '广西省'
END
----------------------------------------------------
--贵州省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '贵州')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '贵州省'	WHERE F_Province = '贵州'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '贵州省'	WHERE CreateUserID = '贵州'
END
----------------------------------------------------
--浙江省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '海盐县康复护理院')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '浙江省'	WHERE F_Province = '海盐县康复护理院'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '浙江省'	WHERE CreateUserID = '海盐县康复护理院'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '杭州市')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '浙江省'	WHERE F_Province = '杭州市'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '浙江省'	WHERE CreateUserID = '杭州市'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '浙江')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '浙江省'	WHERE F_Province = '浙江'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '浙江省'	WHERE CreateUserID = '浙江'
END
----------------------------------------------------
--河北省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '河北')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '河北省'	WHERE F_Province = '河北'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '河北省'	WHERE CreateUserID = '河北'
END
----------------------------------------------------
--河南省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '河南')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '河南省'	WHERE F_Province = '河南'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '河南省'	WHERE CreateUserID = '河南'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '三门峡')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '河南省'	WHERE F_Province = '三门峡'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '河南省'	WHERE CreateUserID = '三门峡'
END
----------------------------------------------------
--黑龙江省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '黑龙江')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '黑龙江省'	WHERE F_Province = '黑龙江'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '黑龙江省'	WHERE CreateUserID = '黑龙江'
END
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '哈尔滨')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '黑龙江省'	WHERE F_Province = '哈尔滨'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '黑龙江省'	WHERE CreateUserID = '哈尔滨'
END
----------------------------------------------------
--湖南省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '湖南')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '湖南省'	WHERE F_Province = '湖南'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '湖南省'	WHERE CreateUserID = '湖南'
END
----------------------------------------------------
--吉林省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '吉林')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '吉林省'	WHERE F_Province = '吉林'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '吉林省'	WHERE CreateUserID = '吉林'
END
----------------------------------------------------
--江苏省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '江苏')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '江苏省'	WHERE F_Province = '江苏'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '江苏省'	WHERE CreateUserID = '江苏'
END
----------------------------------------------------
--江西省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '江西')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '江西省'	WHERE F_Province = '江西'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '江西省'	WHERE CreateUserID = '江西'
END
----------------------------------------------------
--宁夏回族自治区
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '宁夏')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '宁夏回族自治区'	WHERE F_Province = '宁夏'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '宁夏回族自治区'	WHERE CreateUserID = '宁夏'
END
----------------------------------------------------
--山西省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '山西')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '山西省'	WHERE F_Province = '山西'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '山西省'	WHERE CreateUserID = '山西'
END
----------------------------------------------------
--陕西省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '陕西')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '陕西省'	WHERE F_Province = '陕西'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '陕西省'	WHERE CreateUserID = '陕西'
END
----------------------------------------------------
--上海市
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '上海')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '上海市'	WHERE F_Province = '上海'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '上海市'	WHERE CreateUserID = '上海'
END
----------------------------------------------------
--辽宁省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '沈阳')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '辽宁省'	WHERE F_Province = '沈阳'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '辽宁省'	WHERE CreateUserID = '沈阳'
END
----------------------------------------------------
--四川省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '四川')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '四川省'	WHERE F_Province = '四川'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '四川省'	WHERE CreateUserID = '四川'
END
----------------------------------------------------
--湖北省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '武汉')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '湖北省'	WHERE F_Province = '武汉'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '湖北省'	WHERE CreateUserID = '武汉'
END
----------------------------------------------------
--新疆维吾尔自治区
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '新疆')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '新疆维吾尔自治区'	WHERE F_Province = '新疆'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '新疆维吾尔自治区'	WHERE CreateUserID = '新疆'
END
----------------------------------------------------
--云南省
IF EXISTS (SELECT 1 FROM dbo.T_Cus_CustomerBase where F_Province = '云南')
BEGIN
	UPDATE dbo.T_Cus_CustomerBase SET F_Province = '云南省'	WHERE F_Province = '云南'
	UPDATE dbo.T_Wo_WorkOrder SET CreateUserID = '云南省'	WHERE CreateUserID = '云南'
END