在某一列上加上指定的内容
这次我有一张表，表里面有一列电话号码，可惜没有加区号。由于这批电话是全省各地市的，所以区号也不一样。我们的任务就是在号码这一列加上此号码的区号。下面是原表和结果表
--原表
region phone type
-------------------- ---------------------------- --------------------
南阳 65911195 WT
郑州 65972537 WT
-------------------- ---------------------------- --------------------
南阳 65911195 WT
郑州 65972537 WT
--结果表
region phone type
-------------------- -------------------------------- --------------------
南阳 037765911195 WT
郑州 037165972537 WT
-------------------- -------------------------------- --------------------
南阳 037765911195 WT
郑州 037165972537 WT
原理
由于要更新表，所以首先会想到用update语句，可是此批量操作所插入的内容是不同的，用游标效率又不太高。所以我选择用临时表，另一方面临时表的速度比较理想。
下面是我用到的代码：
--===========================
--在电话号码前加上区号
--author：boyi55，date：2006-12-10
--===========================
set nocount on
select region,case region
when '商丘' then '0370'
when '郑州' then '0371'
when '安阳' then '0372'
when '新乡' then '0373'
when '许昌' then '0374'
when '平顶山' then '0375'
when '信阳' then '0376'
when '南阳' then '0377'
when '开封' then '0378'
when '洛阳' then '0379'
when '焦作' then '0391'
when '济源' then '0391'
when '鹤壁' then '0392'
when '濮阳' then '0393'
when '周口' then '0394'
when '漯河' then '0395'
when '驻马店' then '0396'
when '三门峡' then '0397'
end as district,phone,type into #boyi55 from dx
go
if object_id('dx') is not null
delete from dx
go
insert into dx(region,phone,type)
select region,(district+phone) as phone,type from #boyi55
go
if object_id('#boyi55') is not null
drop table #boyi55
go
select * from dx
set nocount off
select region,case region
when '商丘' then '0370'
when '郑州' then '0371'
when '安阳' then '0372'
when '新乡' then '0373'
when '许昌' then '0374'
when '平顶山' then '0375'
when '信阳' then '0376'
when '南阳' then '0377'
when '开封' then '0378'
when '洛阳' then '0379'
when '焦作' then '0391'
when '济源' then '0391'
when '鹤壁' then '0392'
when '濮阳' then '0393'
when '周口' then '0394'
when '漯河' then '0395'
when '驻马店' then '0396'
when '三门峡' then '0397'
end as district,phone,type into #boyi55 from dx
go
if object_id('dx') is not null
delete from dx
go
insert into dx(region,phone,type)
select region,(district+phone) as phone,type from #boyi55
go
if object_id('#boyi55') is not null
drop table #boyi55
go
select * from dx
set nocount off
--首先用case根据region列判断所要加的区号。然后写入到临时表里。再把原表清空，然后将加了区号的数据写入到原表中。代码比较简单，主要是一个思路
本文转自 boyi55 51CTO博客，原文链接：http://blog.51cto.com/boyi55/27298，如需转载请自行联系原作者
