如下所示oracle分页语句怎么改写为mysql分页语句


<![CDATA[
select * from(select rownum as num,U.*,r.roleName from UserInfo u inner join roles r
on u.r_id=r.r_id where rownum<#{countPage,jdbcType=INTEGER}
]]>
and username like #{ke,jdbcType=VARCHAR})
<![CDATA[
where num>#{firtPage,jdbcType=INTEGER}
]]>

2个回答

按照一个字段排序,最后使用limit begin,size 就可以了,begin表示开始位置,size表示你要取的数量

u013750360
小易莱莱 没看懂为什么写两个where,是不是第一个写错了,应该是and;后面的,limit关键字是在where之后,而且,你要分页,肯定要指定初始位置,你的只是查询几条,应该是select * from t1 where t1.a=#{a} limit #{begin},#{size}
10 个月之前 回复
qq_39880085
qq_39880085 <select id="getAll" parameterType="java.util.Map" resultMap="UserMap"> <![CDATA[ select * from(select rownum as num,U.*,r.roleName from UserInfo u inner join roles r on u.r_id=r.r_id ]]> <if test="ke!=null"> where username like #{ke,jdbcType=VARCHAR}</if> <![CDATA[ limit #{countPage,jdbcType=INTEGER} ]]> ) <![CDATA[ where num>#{firtPage,jdbcType=INTEGER} ]]> </select>是这样吗?
10 个月之前 回复

一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点:
第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目
如果只给定一个参数:它表示返回最大的记录行数目
第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
初始记录行的偏移量是 0(而不是 1)

例如:

 SELECT
  U.*,
  r.roleName
FROM
  UserInfo u
INNER JOIN roles r
ON
  u.r_id=r.r_id
WHERE
  username LIKE 'user' limit 1,7;
ththcc
小影__ 回复qq_39880085: 不是
10 个月之前 回复
qq_39880085
qq_39880085 <select id="getAll" parameterType="java.util.Map" resultMap="UserMap"> <![CDATA[ select * from(select rownum as num,U.*,r.roleName from UserInfo u inner join roles r on u.r_id=r.r_id ]]> <if test="ke!=null"> where username like #{ke,jdbcType=VARCHAR}</if> <![CDATA[ limit #{countPage,jdbcType=INTEGER} ]]> ) <![CDATA[ where num>#{firtPage,jdbcType=INTEGER} ]]> </select>这样吗?
10 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
其他相关推荐
mysql与oracle的分页语句
mysql 与 oracle 的分页 语句
Oracle和MySQL分页语句
例子:取用户表(User)年龄(age)最大的前十个人 Oracle分页:select * from (select a.* ,rownum as rownum from (select * from user order by age desc)a where rownum<=10) where rownum>0; Mysql分页:select * from user order by age d
Oracle 和 Mysql 的分页语句
Mysql 分页用limit , select * from tf_b_order limit 1;   --显示第一条数据 select * from tf_b_order limit 2;   --显示2条数据 select * from tf_b_order limit 0,3;    -- 显示第1到3条数据 select * from tf_b_order limit 3,3
oracle的分页语句怎么转换成mysql中的分页语句
/**分页查询Userrn * pageSize:表示一页显示的数据个数;page:表示第几页rn */rn public List findUserByPage(int pageSize, int page) rn String sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn List params=null;rn return daoImpl.findUserByPage(sql, params);rn rn public int getUserCount() rn String sql="select count(*) from users";rn List params=null;rn return daoImpl.getCount(sql, params);rn rn /**rn * 分页查询Billrn * rn */rn public List findBillByPage(int pageSize, int page) rn String sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn List params=null;rn return daoImpl.findBillByPage(sql, params);rn rn public int getBillCount() rn String sql="select count(*) from bill";rn List params=null;rn return daoImpl.getCount(sql, params);rn rn //根据条件获得满足条件的bill的数目rn public int getBillCount(String product_name,String ispay)rn String sql=null;rn List params=new ArrayList();rn if(product_name==null && ispay==null)rn sql="select count(*) from Bill";rn else if(product_name==null && ispay!=null)rn sql="select count(*) from Bill where ispay=?";rn params.add(ispay);rn else if(product_name!=null && ispay==null)rn sql="select count(*) from Bill where product_name like '%"+product_name+"%'";rn elsern sql="select count(*) from Bill where product_name like '%"+product_name+"%' and ispay=?";rn params.add(ispay);rn rn return daoImpl.getCount(sql, params);rn rn //根据条件进行分页rn public List findBillByPage(int pageSize, int page,String product_name,String ispay) rn String sql=null;rn List params=new ArrayList();rn if(product_name==null && ispay==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn else if(product_name==null && ispay!=null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill where ispay=?) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page) ;rn params.add(ispay);rn else if(product_name!=null && ispay==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill where product_name like '%"+product_name+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn elsern sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill where product_name like '%"+product_name+"%' and ispay=?) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page); rn params.add(ispay);rn rn return daoImpl.findBillByPage(sql, params);rn rn /**rn * 分页查询Supplierrn */rn public List findSupplierByPage(int pageSize, int page) rn String sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn List params=null;rn return daoImpl.findSupplierByPage(sql, params);rn rn public int getSupplierCount() rn String sql="select count(*) from Supplier";rn List params=null;rn return daoImpl.getCount(sql, params);rn rn //根据条件查询supplier的个数rn public int getSupplierCount(String supplier_name,String supplier_des)rn String sql=null;rn List params=new ArrayList();rn if(supplier_name==null && supplier_des==null)rn sql="select count(*) from supplier";rn else if(supplier_name==null && supplier_des!=null)rn sql="select count(*) from supplier where supplier_des like '%"+supplier_des+"%'";rn else if(supplier_name!=null && supplier_des==null)rn sql="select count(*) from supplier where supplier_name like '%"+supplier_name+"%'";rn elsern sql="select count(*) from supplier where supplier_name like '%"+supplier_name+"%' and supplier_des like '%"+supplier_des+"%'";rn rn return daoImpl.getCount(sql, params);rn rn //根据条件对supplier的查询结果进行分页rn //根据条件进行分页rn public List findSupplierByPage(int pageSize, int page,String supplier_name,String supplier_des) rn String sql=null;rn List params=new ArrayList();rn if(supplier_name==null && supplier_des==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn else if(supplier_name==null && supplier_des!=null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier where supplier_des like '%"+supplier_des+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page) ;rn else if(supplier_name!=null && supplier_des==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier where supplier_name like '%"+supplier_name+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn elsern sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier where supplier_name like '%"+supplier_name+"%' and supplier_des like '%"+supplier_des+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page); rn rn return daoImpl.findSupplierByPage(sql, params);rn rn //根据条件查询User的个数rn public int getUserCount(String user_name)rn String sql=null;rn List params=new ArrayList();rn if(user_name==null)rn sql="select count(*) from users";rn else if(user_name!=null)rn sql="select count(*) from users where name like '%"+user_name+"%'";rn rn return daoImpl.getCount(sql, params);rn rn //根据条件对User的查询结果进行分页rn //根据条件进行分页rn public List findUserByPage(int pageSize, int page,String user_name) rn String sql=null;rn List params=new ArrayList();rn if(user_name==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn else if(user_name!=null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users where name like '%"+user_name+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn rn return daoImpl.findUserByPage(sql, params);rn rn //根据bill_id查找账单rn public Bill findBillById(String billId) rn String sql="select * from bill where bill_id=?";rn List params=new ArrayList();rn params.add(billId);rn List bills=daoImpl.findAllBill(sql,params);rn if(bills.size()>0)rn return bills.get(0);rn elsern return null;rn rn rn rn //更新账单信息rn public boolean updateBill(Bill bill) rn String sql="update bill set money=?,deal_company=?,product_num=?,product_name=?,product_des=?,ispay=?,supplier_name=? where bill_id=?";rn List params=new ArrayList();rn params.add(Integer.parseInt(bill.getMoney()));rn params.add(bill.getDeal_company());rn params.add(Integer.parseInt(bill.getProduct_num()));rn params.add(bill.getProduct_name());rn params.add(bill.getProduct_des());rn params.add(bill.getIspay());rn params.add(bill.getSupplier_name());rn params.add(Integer.parseInt(bill.getBill_id()));rn return daoImpl.update(sql, params);rn rn //执行删除账单操作rn public boolean deleteBill(String id) rn String sql="delete from bill where bill_id=?";rn List params=new ArrayList();rn params.add(Integer.parseInt(id));rn return daoImpl.update(sql, params);rn rn rn rnrn
mysql 分页语句优化
 测试数据表结构      现在有两张表   customer,以及order_test, customerId的主键是order_test的外键     CREATE TABLE `customer` ( `customerId` int(11) NOT NULL AUTO_INCREMENT, `phone` varchar(11) NOT NULL COMMENT '...
oracle语句分页
1.oracle语句分页: 当结果集不需要进行排序时,每页显示条数为:rowPerPage,当前页数为:currentPage。 1、 相对来说,这种查询速度会快一些,因为当currentPage小时,嵌套语句查询的结果集小。但当currentPage 变大时,查询速度会慢慢变慢。当结果集很大时,查询第一页跟最后一页的速度会有明显变化。(倾向用这种!) select * from...
php分页的MySQL语句
php分页的MySQL语句,里面有详细的分页代码,很适合现在的程序开发
oracle分页sql语句
oracle的分页与mysql的分页是不一样的,mysql的分页网上很好找,但oracle的难找一些。下面就直接上代码了,参照这个格式就可以了。 select distinct code,dates,status from (select t1.*,rownum rn from(select * from YWS_PURCHASE)t1 where rownum='${start}' 因为
Oracle分页语句
select *   from (select *           from (select /*+  index_desc(t,idx_dec_1) */                  t.*, rownum rn                   from T_FXZF t                  where /*reachdate >=20150516 and
Oracle 分页语句
//万能 oracle 分页语句,中间写你需要进行分页操作的sql ,效率排第二,最快的分页方法需要将rownum 嵌入到原sql 里面,下次写 select * from ( select rownum as rn,vo.* from ( // 需要分页的sql 语句 例如:select * from (表名) ) vo where rownum &lt;= #{endIndex}) a...
oracle 分页 语句
  1. select * from ( select t.* , rownum as rn from (select * from test_ogi ) t where rownum &amp;lt; 28 ) b where b.rn &amp;gt; 20   2. select * from ( select t.* , rownum as rn from (select ...
oracle 分页语句
[code=&quot;java&quot;]private static final String QUERYPERPAGESQL = &quot;select * from (select m.*, rownum rn from (select * from music order by id) m where rownum ?&quot;; //该sql语句为每页显示的个数 public List queryPerPag...
Oracle高效分页语句
with t_rowtable as ( select row_number() over(order by field1) as row_number,* from t_table ) select * from t_rowtable where row_number&amp;gt;1 and row_number &amp;lt; 4 order by field1 select * fr...
oracle分页语句
SELECT T_PAGINATION_SUMMY.* FROM (SELECT ROWNUM ROWNUMBER,T_PAGINATION.* FROM (……) T_PAGINATION WHERE ROWNUM ? 1。终点行数 2。起始行数
求oracle分页语句???
请大家帮我写一个oracle sql分页语句,rn如查找第10条至第20条的纪录,查询结果按时间排序。rn不胜感激!
MySQL 分页语句
[code=&quot;java&quot;]private static final String QUERYPERPAGESQL = &quot;select * from book limit ?,?&quot;; //该sql语句实现每页显示的记录条数 //按页获取记录 public List queryPerPage(int page) {//page为当前处于第几页 List list = new Arra...
MYSQL 分页语句
+------+------+-------+rn| id | name | bumen |rn+------+------+-------+rn| 2400 | a | 1 |rn| 2300 | b | 1 |rn| 2200 | a | 2 |rn| 2200 | b | 2 |rn+------+------+-------+rnrnrnrnrn从第3个开始到第4个 根据ID 总共取出2个数据rnrn咋写?
Mysql分页语句
SELECT 字段 FROM 表LIMIT 从某条开始,每页显示的条数
oracle sql分页语句
select * from (select a.*,rownum r from (select * from emp order by HIREDATE) a where rownum &lt; 10) where r &gt; 5;
oracle 语句分页优化
一种常用的方法 rownum方法    select *  from (select x1.*, rownum r          from (select x.* from appsys_objects x order by id) x1         where rownum &amp;lt; 10) where r &amp;gt; 5   二种rowid 方法    select *  fro...
简单的oracle分页语句
[size=large] select * from(select a.*, rownum rn from (select * from table_name) a where rownum = 2 需要注意的是,上面的分页语句,大的数字必须在前面,小的必须在后面,否则查询不了数据,至于为什么,我也不知道 ^_^ select * from(select a.*, rownum rn...
oracle 分页语句问题
select top ‘“+pageSize+”’ * from A where not in (select top ‘“+currPage+”’ID from A)把这个sql语句该成oracle 的怎么改啊?
oracle的 分页sql语句
讲解oracle数据库操作数据表的分页过程,简单明了,可以直接套用,希望对大家学习oracle会有帮助
oracle分页语句。
String sql = "select * from (select rownum rn,news.* from (select * from News where title = "+"'"+title+"' or class="+"'"+classs+"'" + "order by ID ) news) where rn <=" + rowBegin;rnrn我怎么分页查询5个一页 sql语句怎么写?
Mysql的分页与Oracle的分页
SqlServer中的分页: Mssql 2000分页采用top 关键字(20005以上版本也支持关键字rownum) Select top 10 * from t_order where id not in (select id from t_order where id>5 ); // Select top 10 * from t_order where id not in (se
oracle分页和mysql分页
mysql 分页 --查前5 数据 select * from table_name limit 0,5 select * from table_name limit 5 --limit关键字的用法: LIMIT [offset,] rows --offset指定要返回的第一行的偏移量,rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。 oracle ...
MySql分页和Oracle分页
MySql分页 select * from tableName where 条件 limit m,n; limit第一个参数是起始位置,第二个参数是取的条数 PS:默认从0开始,上面代码的意思是从m-1的位置开始,取n条数据 Oracle分页 SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM tabl
mybatis分页查询(Mysql,oracle分页语句)
步骤一:导入相关jar包 步骤二:在src下配置mybatis.xml配置文件。其中对数据库连接,映射文件的加载进行配置。(简写配置可选) 步骤三:建立实体类Student 步骤四:配置映射文件,其中配置好相对应的sql分页查询的语句(这里以mysql为例) 步骤五:dao层中编写分页方法 mysql
DB2,Mysql,Sqlserver,oracle分页算法,分页语句
[size=medium][color=red]Mysql[/color][/size] [code=&quot;sql&quot;] limit = ((feeds.getPageIndex() - 1) * 10 + 1) + &quot;,&quot; + (feeds.getPageIndex() * 10); [/code] [size=medium][color=red]DB2,Sqlserver[/c...
分页语句
数据库分页大全(oracle利用解析函数row_number高效分页) Mysql分页采用limt关键字 select * from t_order limit 5,10; #返回第6-15行数据 select * from t_order limit 5; #返回前5行 select * from t_order limit 0,5; #返回前5行 Mssql 2...
MS SQL分页语句转为oracle语句~
MS SQL写的分页语句,例如:rnselect * from (select top 4 * from (select top 19 a.id,a.title,a.ctime,a.doshow,b.cnname from apw_infoems a,apw_ad b where a.types='1' and a.active='1' and a.creater=b.userid order by a.ctime desc) derivedtbl order by ctime) derivedtbl order by ctime descrn和rnselect * from (select top 19 * from (select top 19 * from apw_infoems where active='1' and types='1' order by createtime desc) derivedtbl order by createtime) derivedtbl order by createtime descrnrn这2个语句转为oracle的应该怎么写?rn谢谢rnrn
oracle 分页SQL语句改为HQL 语句
如何将oracle分页的SQL语句改为HQL语句,请大家帮帮忙!写好了,分全给你.rn-----------------------------rnSELECT * FROM rn(rnSELECT A.*, ROWNUM RN rnFROM (SELECT * FROM TABLE_NAME) A rnWHERE ROWNUM <= 40rn)rnWHERE RN >= 21rn-----------------------------rn这是读取数据库中的20-40的记录,帮我改为HQL语句,谢谢!在线等!
sql2000分页存储过程,怎么传语句分页?
因为我是多个表关联 分页rnrnselect a.*,n.mc from a left join b on a.id=b.aidrnrn效率 要比not in 分页高的存储过程
Oracle中的分页语句怎么写?
SQL Server中可以这样写: Select Top 20 * from aTablernOracle中怎么写?
MySQL 分页sql语句练习
[align=center][/align][align=left][/align]取出sql表中第31到40的记录(以自动增长ID为主键) select top 10 * from t where id not in (select top 30 id from t order by id ) order by id; select top 10 * from t where id...
Oracle SQL语句分页问题
关于Oracle SQL语句分页问题:这里有一些实例,仅供大家下载来参考,如果有更好、更简便的方法,可以跟帖一起交流一下
oracle的sql语句的排序和分页
oracle与mysql不一样,在使用select查询时,oracle查询在不加order by时,查询的数据是有随机性,
相关热词 c#检测非法字符 c#双屏截图 c#中怎么关闭线程 c# 显示服务器上的图片 api嵌入窗口 c# c# 控制网页 c# encrypt c#微信网页版登录 c# login 居中 c# 考试软件