龙空技术网

mysql 用 rownum := rownum+1 实现 Oracle 的 rownum 功能

淮安二傻子 148

前言:

目前你们对“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数据库、

标签: #oracle order by rownum