select * from
(
SELECT A.*,ROWNUM RN
FROM (SELECT * FROM 表A where 列名='条件'
and
列名='条件'
and 列名 in (select 条件 from 表B where 列名='条件')) A
)
WHERE RN BETWEEN 1 AND 20; =====================
(1):先查询满足我需要的数据和rownum
==========================================
SELECT A.*,ROWNUM RN
FROM (SELECT * FROM 表A where 列名='条件'
and
列名='条件'
and 列名 in (select 条件 from 表B where 列名='条件')) A ================
然后:
select * from
(
)
WHERE RN BETWEEN 开始数 AND 结尾数;
截取需要的字段!
========
结果:报无效数字错误,发现第一步语句正常,可以在这段出现问题:
select * from
(
)
WHERE RN BETWEEN 开始数 AND 结尾数;
请问是不是ROWNUM RN在查询出来时候变成了字符串,结果导致出现“无效数字”错误!!!
我再给你一个效率高的select *
from (SELECT 表A.*, rownum rn
FROM 表A
where 列名 = '条件 '
and 列名 = '条件 '
and 列名 in (select 条件 from 表B where 列名 = '条件 ')
and rownum < 20)
where rn >= 1
你的sql语句没问题,不过既然是分页,那么你的1、20应该是程序传过来的,检查一下这两个参数是不是都是数字。
Create or replace package pkg_mBlog_se
as
type myrctype is ref cursor;
procedure mBlog_se_proc(v_ownername varchar2,v_fromdate varchar2,v_todate varchar2,v_page number default 1,v_pageSzie number default 20,v_num out number,p_rc out myrctype);
end pkg_mBlog_se;
/--------------------------------------------------------------------
create or replace package body pkg_mBlog_se
as
procedure mBlog_se_proc(v_ownername varchar2,v_fromdate varchar2,v_todate varchar2,v_page number default 1,v_pageSzie number default 20,v_num out number,p_rc out myrctype)
is
sqlstr VARCHAR2(2000);
begin
sqlstr :=
'INSERT INTO mBlog_tmp_proc(rid,id,ownername,nickname,title,content,createdate,modifieddate)
SELECT ROW_NUMBER() over(order by blogCreationDate desc) as rid, BlogID as id
Blogownername as ownername, Blognickname as nickname,Blogtitle as title,BlogContent as content,to_char(BlogCreationdate,''YYYY-MM-DD HH24:MI:SS'') as createdate,to_char(BlogmodifiedDate ,''YYYY-MM-DD HH24:MI:SS'') as modifieddate FROM mvnforumBlog';
if v_ownername is not null then
sqlstr :=sqlstr||' and Blogownername='''||v_ownername||'''';
end if;
if v_fromdate is not null then
sqlstr :=sqlstr||' and BlogCreatedate>=to_date('''||v_fromdate||''',''YYYY-MM-DD'')';
end if;
if v_todate is not null then
sqlstr :=sqlstr||' and BlogCreatedate<to_date('''||v_todate||''',''YYYY-MM-DD'')+1';
end if;
execute immediate sqlstr;
select count(*) into v_num from mBlog_tmp_proc;
sqlstr :='SELECT t.rid ,t.blogid, t.ownername,t.nickname,t.title,t.content,t.createdate,t.moidfieddate from mBlog_tmp_proc t ';
sqlstr :=sqlstr||' where t.rid>(:v_page-1)*:v_pagesize and t.rid <=:v_page*:v_pagesize';
OPEN p_rc FOR sqlstr USING v_page, v_pageSize, v_page, v_pageSize;
COMMIT;
end mBLog_se_proc;
--end pkg_mBlog_Imsi_Se;
end pkg_mBlog_se;
/
select * from
(
SELECT A.*,ROW_NUMBER() OVER(ORDER BY A.ID) AS RN
FROM (SELECT * FROM 表A where 列名= '条件 '
and
列名= '条件 '
and 列名 in (select 条件 from 表B where 列名= '条件 ')) A
)
WHERE RN BETWEEN 1 AND 20;
select *
from (SELECT 表A.*, rownum rn
FROM 表A
where 列名 = '条件 '
and 列名 = '条件 '
and 列名 in (select 条件 from 表B where 列名 = '条件 ')
and rownum < 20)
where rn >= 1
这样写效率最高!!