sql常用语句100例

--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