Jet Sql 与MSSql中有区别的几条语句

在做access转向sqlserver的程序过程中，发现的两个平台sql语句的区别：

1.Jet sql执行全连接要采用union all

如果要从全连接查询比如

Select xcode,xyear,dlh,sum(d100)  from ( select * from bgb union all select * from bgb02 ) group by xcode,xyear,dlh

再sqlserver中执行要在group 前加DERIVEDTBL

Select xcode,xyear,dlh,sum(d100)  from ( select * from bgb union all select * from bgb02 )  DERIVEDTBL  group by xcode,xyear,dlh

2.update语句牵扯到多个表比如

 

UPDATE Kbgb AS k1, kbgb02 AS k2 SET k1.d001 = k2.d001

WHERE  k1.kdlh=k2.kdlh And k1.xyear=k2.xyear And k1.xcode=k2.xcode 支持别名

 

而在sqlserver中必须要写

update Kbgb04 set kbgb04.d001=kbgb.d001

from kbgb WHERE (kbgb04.kdlh=kbgb.kdlh) and (kbgb04.xyear=kbgb.xyear) and (kbgb04.xcode=kbgb.xcode)

不支持别名

 

3.jet sql 支持类似以下插入语句：

Insert into table1 select * from table2 有时候两个表字段个数不同，但table1中字段名称与table2中字段名称相同的情况下可正确执行,但在sqlserver中必须两者字段完全一致，或者在sqlserver中必须把table1的字段写全了

Insert into table1（col1，col2） select * from table2

4.jet Sql中trim（）函数sqlserver中不支持，sqlserver中key是关键字，access中不是。

5.行列转换，access支持TRANSFORM 和pivot,比如：

transform  Sum(kbgb.d004)   AS  d004 SELECT   kbgb.xcode,'2008' as xyear FROM   kbgb  where kbgb.xyear='2007' GROUP   BY  kbgb.xcode,kbgb.xyear PIVOT   kbgb.kdlh,

MSSql 不支持，在MSSql中实现可以参考邹建的存储过程：

 
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_qry]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_qry]  
  GO  
   
 /*--生成交叉表的简单通用存储过程  
   
  根据指定的表名,纵横字段,统计字段,自动生成交叉表  
  并可根据需要生成纵横两个方向的合计  
   
  注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段  
  如果不要此功能,则去掉交换处理部分  
   
  --邹建   2004.06(引用请保留此信息)--*/  
   
  /*--调用示例  
   
  exec   p_qry   'syscolumns','id','colid','colid','name   like   ''s%''',1,1  
  --*/  
   
  create   proc   p_qry  
  @TableName   sysname, --表名  
  @纵轴   sysname, --交叉表最左面的列  
  @横轴   sysname, --交叉表最上面的列  
  @表体内容   sysname, --交叉表的数数据字段  
  @条件   varchar(1000),--查询的处理条件  
  @是否加横向合计   bit, --为1时在交叉表横向最右边加横向合计  
  @是否家纵向合计   bit --为1时在交叉表纵向最下边加纵向合计  
  as  
  declare   @s   nvarchar(4000),@sql   varchar(8000)  
   
  --规范条件  
  set   @条件=case   when   @条件<>''   then   '   where   ('+@条件+')'   else   ''   end  
   
  --判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段  
  set   @s='declare   @a   sysname  
  if(select   case   when   count(distinct   ['+@纵轴+'])
  from   ['+@TableName+']   '+@条件+')=1  
  select   @a=@纵轴,@纵轴=@横轴,@横轴=@a'  
  exec   sp_executesql   @s  
  ,N'@纵轴   sysname   out,@横轴   sysname   out'  
  ,@纵轴   out,@横轴   out  
   
  --生成交叉表处理语句  
  set   @s='  
  set   @s=''''  
  select   @s=@s+'',[''+cast(['+@横轴+']   as   varchar)+'']=sum(case   ['+@横轴  
  +']   when   ''''''+cast(['+@横轴+']   as   varchar)+''''''   then   ['+@表体内容+']   else   0   end)''  
  from   ['+@TableName+']  
  '+@条件+'  
  group   by   ['+@横轴+']'  
  exec   sp_executesql   @s  
  ,N'@s   varchar(8000)   out'  
  ,@sql   out  
   
  --是否生成合计字段的处理  
  declare   @sum1   varchar(200),@sum2   varchar(200),@sum3   varchar(200)  
  select   @sum1=case   @是否加横向合计    
  when   1   then   ',[合计]=sum(['+@表体内容+'])'  
  else   ''   end  
  ,@sum2=case   @是否家纵向合计    
  when   1   then   '['+@纵轴+']=case   grouping(['  
  +@纵轴+'])   when   1   then   ''合计''   else   cast(['  
  +@纵轴+']   as   varchar)   end'  
  else   '['+@纵轴+']'   end  
  ,@sum3=case   @是否家纵向合计  
  when   1   then   '   with   rollup'  
  else   ''   end  
   
  --生成交叉表  
  exec('select   '+@sum2+@sql+@sum1+'  
  from   ['+@TableName+']  
  '+@条件+'  
  group   by   ['+@纵轴+']'+@sum3)  
  go