mssql sqlserver union关键字同order by 关键字一起报错的处理方法分享


摘要:
下文讲述union关键字和order by 关键字一起应用时,sql产生错误的处理方法,如下:
实验环境:sql server 2008 R2


例:
员工表 包含(姓名,年龄)两个字段
大客户表 包含(姓名,年龄)两个字段

   select [姓名],[年龄]  from [员工表] order by [姓名] 
   union 
   select [姓名],[年龄]  from [大客户表] order by [姓名] 
   ---此脚本的运行,会产生union附近存在脚本错误的现象 
  


在sqlserver脚本编写中,当order by 和union同处一层时,此时sql脚本会产生相应的错误,因为需将order by关键字放入外层或内层后,此异常久得到解决了

正确的order by 同union的写法如下所示:

  ---写法1:
  select * from ( select [姓名],[年龄]  from [员工表] order by [姓名] ) A
   union 
  select * from ( select [姓名],[年龄]  from [大客户表] order by [姓名] )B
  
  ---写法2:
   select * form 
  (
     select [姓名],[年龄]  from [员工表]  
   union 
   select [姓名],[年龄]  from [大客户表] 
  ) order by [姓名]