昨天需要写一个简单SQL,实现目标如下:
给数据库中TelNum(存储用户电话信息)一列添加前缀 +86-431- (随便拿个区号说明),如果原来的列是空值,则不作处理。
写完后发现执行效率极低,因为写SQL很少,我就直接用游标去做处理了,后来领导一看,笑我老土,让我去查查Case的用法,于是第二种处理就产生了,将两种方法都贴出来方便学习。
看来我得SQL弱的一塌糊涂,还得赶紧学习。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
游标方式
/*游标重新对TelNum赋值,空则不变,有值则赋前缀+86-431-,使用游标完成*/
Declare @TelNum nvarchar(20),@id nvarchar(50)
Declare UpdateTelNum_Cursor CURSOR
For select [TelNum],[id] from PersonForTel
Open UpdateTelNum_Cursor
Fetch Next From UpdateTelNum_Cursor Into @TelNum,@id
While @@FETCH_STATUS = 0
Begin
If @TelNum <> ''
Begin
Update PersonForTel Set [TelNum] = '+86-431-' + [TelNum] where [id]=@id
End
Fetch Next From UpdateTelNum_Cursor Into @TelNum,@id
End
Close UpdateTelNum_Cursor
DEALLOCATE UpdateTelNum_Cursor
/*用Case做判断,同样可用于Select语句*/
Update PersonForTel Set [TelNum] =
Case [TelNum]
When '' Then ''
Else '+86-431-' + [TelNum]
End
一个简单的Case例子:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
select departmentname=
case
when s.parentid > 9 then (select s1.longname from saporg as s1 where s1.selfid=s.parentid )
else s.longname
end ,
s.longname,p.sname,p.TelNum
from personfortel as p left join saporg as s on p.orgeh=s.dpnum
where longname <> ''
order by departmentname desc,longname asc