关于oracle的分页查询
select b.* from (select a.*,rownum rnum from (select * from (select dp.*,db.* from d_category_product dcp join d_product dp on(dcp.product_id=dp.id) join d_book db on(dp.id=db.id) where dcp.cat_id=1)) a where rownum<=2)b where rnum>=1;
第 1 行出现错误:
ORA-00918: 未明确定义列
求大神帮助,下面是3个表
CREATE TABLE d_product (
id number(12) NOT NULL PRIMARY KEY,
product_name char(100) NOT NULL,
description char(100) default NULL,
add_time number(20) default NULL,
fixed_price number(10,2) NOT NULL,
dang_price number(10,2) NOT NULL,
keywords char(200) default NULL,
has_deleted number(1) default '0',
product_pic char(200) default NULL);
CREATE TABLE d_book (
id number(12) NOT NULL PRIMARY KEY,
author char(200) NOT NULL,
publishing char(200) NOT NULL,
publish_time number(20) NOT NULL,
word_number char(15) default NULL,
which_edtion char(15) default NULL,
total_page char(15) default NULL,
print_time number(20) default NULL,
print_number char(15) default NULL,
isbn char(25) default NULL,
author_summary varchar2(200) NOT NULL,
catalogue varchar2(200) NOT NULL);
CREATE TABLE d_category_product (
id number(12) NOT NULL PRIMARY KEY,
product_id number(10) NOT NULL,
cat_id number(10) NOT NULL);
[最优解释]
你写的子查询语句有问题,
rownum是一个伪列,你没有指明返回伪列,使用"*"是无法返回伪列的,在Oracle里面分页最快的是结合rowid和rownum使用,e.g.
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from
t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
[其他解释]
oracle的你去oracle专区问吧