sqlserver中字段更新（'男'改为'女'，'女'改为'男'）

    朋友提出的一个字段更新问题。

    问题：Sqlserver中ConfigMgr中有一张表为Person,有一个字段为sex char(4)，保存性别（‘男’或‘女’），该表中有多条记录，如何用一条语句实现将‘男’改为‘女’，‘女’改为‘男’?

 

    测试目标数据库版本为：Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

    首先向Person表插入10万条，脚本如下：

   declare  @i  int 
   set  @i = 0
   while  @i < 10000
   begin
   if  @i % 2 = 1
   insert  into  [ ConfigMgr ]. [ dbo ]. [ Person ]
   values( ' 男 ')
   else 
   insert  into  [ ConfigMgr ]. [ dbo ]. [ Person ]
   values( ' 女 ')
   set  @i = @i + 1

  end 

    我的解决方法如下： 

   语句1：

   declare  @d  datetime
   set  @d = GETDATE()
  
   update   [ ConfigMgr ]. [ dbo ]. [ Person ]
   set Sex =
   case 
   when Sex = ' 男 '  then  ' 女 '
   when Sex = ' 女 '  then  ' 男 '
   end
  
   select Elapsed_MillionSecond = DATEDIFF(ms, @d, getdate())

 

  语句2：

   declare  @d  datetime
   set  @d = GETDATE()
  
   update  t1
   set t1.sex =t2.sex
   from  [ ConfigMgr ]. [ dbo ]. [ Person ]  as  t1, [ ConfigMgr ]. [ dbo ]. [ Person ]  as t2
   where t1.Sex !=t2.Sex

   select Elapsed_MillionSecond = DATEDIFF(ms, @d, getdate())

 

    性能分析： 

    首先执行set statistics profile on  来查看语句的具体执行过程，同时选中查看执行计划和客户端统计信息，可以看到语句1和语句2的执行的相关信息。

    当Person表中数据量10万条时，两种方法的差别不是太大，当表中数据量增大到100万时，两种方法的性别差异就比较大了，对比可知语句1的执行效率明显要高于语句2，对比截图如下所示：

    Person中表数据量10万条时：

    语句1的执行10次的客户端统计信息如下图所示：

 

   

     语句1的执行计划图如下所示：

  

    语句1的具体执行过程如下图所示：

 

     语句2的执行10次的客户端统计信息如下图所示：

 

    语句2的执行计划图如下所示：

   

     语句2的具体执行过程如下图所示：

 

    通过对比语句1与语句2的客户端统计图可以看到两语句分别所耗时间及执行过程的具体操作步骤，在10条时，语句1操作10次平均耗时为：368.4毫秒，而语句2平均耗时为：808.7毫秒，两者相关约一倍时间。

 

    下面看一下当Person表中数据量为100万时，语句1与语句2的执行10次的客户端统计情况对比对比，执行过程与10万条时是相同的，只是数据量大小不一样了。

  

    语句1的执行10次的客户端统计信息如下图所示：

 

    语句2的执行10次的客户端统计信息如下图所示：

    通过上图的对比，可以很清楚的看到语句1的执行时间相比语句2要缩短约4倍，数据量越大时，两者的性能差异也就越大。语句1的性能要优于语句2。

 

    另：如果是在oracle中，用decode函数可以轻松解决。

   

   

   

 

转载于:https://www.cnblogs.com/waterfrost/archive/2012/03/27/Sql.html