| 一、有区别的函数及解决方案
以下所示的解决方案中的函数定义在untDataBase单元中TAdoConn类的方法中。
| 序号 | 简述 | Access语法 | SqlServer语法 | Oracle语法 | DB2语法 | 解决方案 | | 01 | 系统时间 | Date() | GETDATE() | SYSDATE | | GetSysTimeStr | | 02 | 连接字符串 | & | + | || | + | GetConcatStr | | 03 | 截取字符串 | SubString | SubStr | SubString | SubString | GetSubStr | | 04 | 小写字符串 | LCase | Lower | Lower | Lower | GetLowerStr | | 05 | 大写字符串 | UCase | Upper | Upper | Upper | GetUpperStr | | 06 | 查找字符串 | InStr | InStr | CharIndex | InStr | GetFindStr | | 07 | 替换空值 | IIF+IsNull | Coalesce | Nvl | Coalesce | GetNullStr | | 08 | 条件取值 | IIF | Case+When+Else | DeCode或Case | IIF | GetCaseStr | | 09 | 字段类型转换 | Str、var、…. | Convert或cast | To_Char,To_Number. | GetConvertStr | GetConvertStr | | 10 | 日期字符串
| ‘2004-10-9’
| #2004-10-19# | ‘2004-10-9’ | | GetDateStr | | 11 | 最大值加1
| | | | | GetNextNumStr | | 12 | Like语句函数 | Like ‘101* | Like ‘101%’ | Like ‘101%’ | | GetLikeStr | | | | | | | | |
二、Access与SQLSERVER部分相同数据库函数及关键字列表 1、 函数
序号
| 简述 | | | 01 | 记数函数
| Count | | 02 | 最大值 | Max | | | | |
2、 关键字
序号
| 简述 | | | 01 |
| Like | | 02 | 连接 | Join | | 03 | 判断空 | Is Null | | | | |
三、Access与语句SqlServer的语句语法区别 1、 Inser Into …..Select …From 语句： 在ACCESS中以下语句 Insert INTO PubSubJectAccCopys(Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) (Select 200201,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03') 中后面"(select 200201******.co_Type='03')"中的小括号("(",")")必须去掉才能执行，如下： Insert INTO PubSubJectAccCopys(Copy_id,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index) Select 200201,Acc_id,Acc_Pid,Acc_name,acc_short,Acc_Comment,Acc_Pro,acc_type,Sub_id_flag,acc_index FROM PubSubJectAcc Where PubSubJectAcc.co_type='03' 在SQL SERVER 中都可以 2、 Inner Join 语句1 StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c.copy_id=c.copy_id) on a.user_id=c.user_id where a.user_opcode=''' +EdtUserOpCode.text+''' And copy_name='''+Tmpcopyname +''''; 应该改为 StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c.copy_id=d.copy_id) on a.user_id=c.user_id where a.user_opcode=''' +EdtUserOpCode.text+''' And copy_name='''+Tmpcopyname +''''; 该行代码的检索条件错误：应该把C.copy_id=C.Copy_id 改为c.copy_id=d.copy_id 注：两种写法都能在SQL-SERVER中运行，但c.copy_id=C.copy_id在ACCESS中不能运行 3、 Inner Join 语句2 StrSql:='select copy_year,copy_name,a.copy_id from SysCopys a inner join SysCopysUser b on a.curcopy_flag=1 and a.copy_id=b.copy_id where b.user_id=' + '''' +TmpPubUserID+ ''''; 该为 StrSql:='select copy_year,copy_name,a.copy_id from SysCopys a inner join SysCopysUser b on a.copy_id=b.copy_id where a.curcopy_flag=''1'' and b.user_id=' + '''' +TmpPubUserID+ ''''; 注：两种写法都能在SQL-SERVER中运行，但第一种在ACCESS中不能运行 4、 Inner Join语句3 SQl server 中可以执行以下语句 'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id AND sysroleoption.role_id=:roleid' 但ACCESS中不能，只能 'Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort From sysoption inner join sysroleoption ON sysoption.opti_id=sysroleoption.opti_id Where sysroleoption.role_id=:roleid' 5、 Update语句 Sql SerVer 中能执行但Access 中不能 'Update sysuserrole SET sysuserrole.role_sort = (Select sysrole.role_sort FROM sysrole Where sysuserrole.role_id = sysrole.role_id and sysuserrole.user_id='01')' 6、 日期比较 SQL SERVER 中用 StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date,End_date ' +'From SysCopys ' +'where copy_id='''+LoginCopyID+''' ' +'and start_date<='''+datetostr(LoginDate)+''' ' +'and end_date>='''+datetostr(LoginDate)+''''; ACCESS中用 StrSql:='select copy_year,Start_month,Cur_month,Start_Flag,Start_date,End_date ' +'From SysCopys ' +'where copy_id='''+LoginCopyID+''' ' +'and start_date<=#'+datetostr(LoginDate)+'# ' +'and end_date>=#'+datetostr(LoginDate)+'#' 参考以上的第10个函数“GetDateStr” 7、 最大数值获取语句 StrSql:='insert into sysRoleOption ' +'select '''+fidRoleId+''' as Role_ID,opti_id,' +'convert(numeric,opti_id)-(convert(numeric,opti_parentid)*100)+'+ MaxOptiSort +' as opti_Sort from sysoption where opti_parentid=''' +PCoTypeID(self.trvRoles.Selected.data)^.StrCoTypeID +''' and opti_bottom=''1'+''''; 改为 StrSql:='insert into sysRoleOption ' +'select '''+fidRoleId+''' as Role_ID,opti_id,' +'opti_id-opti_parentid*100+'+ MaxOptiSort +' as opti_sort from sysoption where opti_parentid=''' +PCoTypeID(self.trvRoles.Selected.data)^.StrCoTypeID +''' and opti_bottom=''1'+'''' 注：两种写法都能在SQL-SERVER中运行，但第一种在ACCESS中不能运行 但是考虑会出现Null值以及语句的通用性，可以使用以上的第07个函数“GetNullStr”和第09个函数“GetConvertStr”来完成字符串向数字，空值和0数字的转换：参考GetNextNumStr代码。 1、Sql中不能省略as
2、一次只能执行一条Sql
3、没有substring、cast等函数
4、sql中严格区分整形和字符型
5、没有存储过程、触发器
6、！= 替换为<>
7、时间字符串两边加#号
8、带参数的sql语句中 @用?号替换 <script type="text/javascript">
</script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script> name="google_ads_frame" marginwidth="0" marginheight="0" src="http://pagead2.googlesyndication.com/pagead/ads?client=undefined&dt=1191934517187&lmt=1191934517&format=undefinedxundefined&output=html&correlator=1191934517187&url=http%3A%2F%2Fbokee.shinylife.net%2Fblog%2Farticle.asp%3Fid%3D557&ref=http%3A%2F%2Fwww.google.cn%2Fsearch%3Fcomplete%3D1%26hl%3Dzh-CN%26inlang%3Dzh-CN%26newwindow%3D1%26q%3Ddb2%2Biif%26btnG%3DGoogle%2B%25E6%2590%259C%25E7%25B4%25A2%26meta%3Dlr%253Dlang_zh-CN%26aq%3Dt%26oq%3Dnull&cc=100&ga_vid=1100622778.1191934388&ga_sid=1191934388&ga_hid=160178139&ga_fc=true&flash=9&u_h=768&u_w=1024&u_ah=738&u_aw=1024&u_cd=32&u_tz=480&u_his=2&u_java=true" frameborder="0" scrolling="no" allowtransparency="allowtransparency"> | 
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
1857
