前言:
目前你们对“oracle order by rownum”大体比较关心,看官们都需要知道一些“oracle order by rownum”的相关文章。那么小编在网上搜集了一些对于“oracle order by rownum””的相关资讯,希望看官们能喜欢,同学们一起来了解一下吧!MySql几乎模拟了Oracle,SqlServer等商业数据库的大部分功能,但是直到现在为止,还是不能使用Oracle的RowNum,这个我们可以自己做些修改,实现这个功能。
大概代码如下所示:
SELECT @rownum:=@rownum+1 AS rownum, weinews.* FROM (SELECT @rownum:=0) r, weinews;
执行结果如下:
给大家展示一下Oracle,SqlServer,MySql三个数据库不同的分页代码:
<cfif Session.dbinfo eq "oracle">
SELECT order_data.*, rownum as rn FROM(
SELECT webhistoryinfo.*
FROM webhistoryinfo
where isdataeffid=1 and isdisplay=1 and msgtype='#msgtype#' and isvideo=0 #PreserveSingleQuotes(SQLStr)# order by createdate)
order_data
<cfelseif Session.dbinfo eq "sqlserver">
SELECT webhistoryinfo.*, row_number() over(order by createdate) as RN
FROM webhistoryinfo
where isdataeffid=1 and isdisplay=1 and msgtype='#msgtype#' and isvideo=0 #PreserveSingleQuotes(SQLStr)#
<cfelseif Session.dbinfo eq "mysql">
SELECT order_data.*, @rownum := @rownum+1 as rn FROM(
SELECT webhistoryinfo.*,@rownum:=0
FROM webhistoryinfo
where isdataeffid=1 and isdisplay=1 and msgtype='#msgtype#' and isvideo=0 #PreserveSingleQuotes(SQLStr)# order by createdate)
order_data
</cfif>
通过这样简单的调整,项目分页代码就可以用很小的代价从Oracle转换到MySql数据库、