--update phoneinfo set cityname = '克孜勒苏柯尔克孜' where cityname = '克孜勒苏柯尔克孜州'
--update phoneinfo set cityname = '湘西' where pad1 = '湖南 吉首'
select * from dbo.PhoneInfo
--update dbo.PhoneInfo set provincename=b.provincename,cityname=b.cityname from dbo.PhoneInfo a,PhoneInfo_hl b
--where a.phonebound=b.phonebound
--select * from dbo.UnknowPhoneBound
--select * from dbo.Area
--select * from phoneinfo a, phoneinfo_old b, phoneinfo_hl c where a.phonebound = b.phonebound and a.phonebound = c.phonebound and (a.cityname <> b.cityname or a.cityname <> c.cityname)
--select * from phoneinfo a, phoneinfo_hl b where a.phonebound = b.phonebound and a.cityname <> b.citynameselect * from phoneinfo a, phoneinfo_old b where a.phonebound = b.phonebound and a.cityname <> b.cityname
--select * into phoneinfo_bak from phoneinfo
--select * from phoneinfo_bak
select * from phoneinfo a, phoneinfo_old b where a.phonebound = b.phonebound and a.cityname <> b.cityname
--select * from dbo.PhoneInfo_Telecom
--update PhoneInfo_Telecom set provincename = '内蒙古' where pad1 = '内蒙兴安盟'
update PhoneInfo set cityname = '酒泉' where pad1 = '甘肃 酒泉嘉峪关'
--update dbo.PhoneInfo_old set provincename=b.provincename,cityname=b.cityname from dbo.PhoneInfo_old a,PhoneInfo_Telecom b
--where a.phonebound=b.phonebound
create table client_all
as
select t2.mobilephone,
t2.clientName,
t2.sex,
t2.birthdayyear,
t2.birthdaymonth,
t2.birthdaydate,
t2.cardtype,
t2.FundPurchaseTotalFee,
t2.InsurePurchaseTotalFee,
t2.FundSource,
t2.InsureSource,
t2.BankSource
from (select mobilephone,
clientName,
Sex,
birthdayyear,
birthdaymonth,
birthdaydate,
cardtype,
fund_sum_fee FundPurchaseTotalFee,
insure_sum_fee InsurePurchaseTotalFee,
case when sourceindustry = '±£ÏÕ'then'±£ÏÕ'end as FundSource,
case when sourceindustry = '»ù½ð'then'»ù½ð'end as InsureSource,
case when sourceindustry = 'ÒøÐÐ'then'ÒøÐÐ'end as BankSource from (select c.*, f.fund_sum_fee, insure_sum_fee
from client_55 c
left join (select sum(cast(fee as float)) fund_sum_fee,
mobilephone
from fund_client_behavior_55
group by mobilephone) f on c.mobilephone =
f.mobilephone
left join (select sum(cast(fee as float)) insure_sum_fee,
mobilephone
from client_purchase_55
group by mobilephone) i on i.mobilephone =
c.mobilephone
) t
) t2。