原有sql(查询所有数据):
SELECT
c. ID,
A . NAME AS NAME,
A .define_sql AS defineSql,
A .use_scene AS useScene ,
A.config_kind AS configKind,
c.examine_status AS examineStatus,
c.file_name AS fileName,
c. LIFE,
A.issue_unit AS issueUnit,
A.issue_time AS issueTime
FROM
(
SELECT
D.ID,
D.apply_id,
D.user_id,
D.file_name,
D.examine_status,
case when D.period_flag = 1 then
'永久' else
D.period_start_time||
'~'||
D .period_end_time
end
as life
FROM
DBM_CUSTOM_DATA_SUBSCRIPTION D
ORDER BY
D .apply_time DESC
) c
LEFT JOIN DBM_APPLY_CONFIG A ON c.apply_id = A . ID
where 1=1
AND a.config_kind =2 and c.user_id ='a054fdd9-9aa5-44b0-9f20-657535ebfd11'
AND c.examine_status IN(0,1,2,3)
改版后的分页sql
第一页,一页展示5条
select tmp_page.* from (
SELECT
c. ID,
A . NAME AS NAME,
A .define_sql AS defineSql,
A .use_scene AS useScene ,
A.config_kind AS configKind,
c.examine_status AS examineStatus,
c.file_name AS fileName,
c. LIFE,
A.issue_unit AS issueUnit,
A.issue_time AS issueTime,
rownum rn
FROM
(
SELECT
D.ID,
D.apply_id,
D.user_id,
D.file_name,
D.examine_status,
case when D.period_flag = 1 then
'永久' else
D.period_start_time||
'~'||
D .period_end_time
end
as life
FROM
DBM_CUSTOM_DATA_SUBSCRIPTION D
ORDER BY
D .apply_time DESC
) c
LEFT JOIN DBM_APPLY_CONFIG A ON c.apply_id = A . ID
where 1=1
AND a.config_kind =2 and c.user_id ='a054fdd9-9aa5-44b0-9f20-657535ebfd11'
AND c.examine_status IN(0,1,2,3)
and rownum <=10
) tmp_page where rn>=6
这个sql的结构为
select tmp_page.* from (SELECT 自己的表字段,rownum rn from 自己的表 where 1=1 and rownum <=5 and 自己sql的过滤条件)tmp_page where rn>=1