SELECT * FROM ( SELECT row_.*, rownum rownum_ from (
SELECT S.ID,S.CONTRACT,S.ERATE,S.INFORMATIONFEE,S.PLACE,S.MEMO,S.INPUTDATE
FROM NEW5I5JDBA.CONSUME S WHERE 1=1
AND S.COMPANYSID = 1 and S.contract like '%M%'
order by S.ID ASC ) row_
WHERE rownum<=23+15 ) WHERE rownum_ >=16
我想显示每页23条 ,16代表16页!
可是我看到我分出来的数据时一条一条蹦出来的!比如,15页分出来23条,16页分出来的只有一条是新的,剩下的22条都是旧的!我就不知道我那写错了,为什么不是23条一个新页呢?
SELECT S.ID,S.CONTRACT,S.ERATE,S.INFORMATIONFEE,S.PLACE,S.MEMO,S.INPUTDATE
FROM NEW5I5JDBA.CONSUME S WHERE 1=1
AND S.COMPANYSID = 1 and S.contract like '%M%'
order by S.ID ASC ) row_
WHERE rownum<=23+15 ) WHERE rownum_ >=16
我想显示每页23条 ,16代表16页!
可是我看到我分出来的数据时一条一条蹦出来的!比如,15页分出来23条,16页分出来的只有一条是新的,剩下的22条都是旧的!我就不知道我那写错了,为什么不是23条一个新页呢?
rownum> n*23
rownum(n+1)*23 ....
要类似这样的代码吧
23+15 我也没看懂···
SELECT S.ID,S.CONTRACT,S.ERATE,S.INFORMATIONFEE,S.PLACE,S.MEMO,S.INPUTDATE
FROM NEW5I5JDBA.CONSUME S WHERE 1=1
AND S.COMPANYSID = 1 and S.contract like '%M%'
order by S.ID ASC ) row_
WHERE rownum<=23*16 ) WHERE rownum_ >=15*23
SELECT S.ID,S.CONTRACT,S.ERATE,S.INFORMATIONFEE,S.PLACE,S.MEMO,S.INPUTDATE
FROM NEW5I5JDBA.CONSUME S WHERE 1=1
AND S.COMPANYSID = 1 and S.contract like '%M%'
order by S.ID ASC ) row_
WHERE rownum<=23+15 ) WHERE rownum_ >=16
select * from table where rownum_=16 limit (currentPage - 1) * pageSize,pageSize
rownum_为查询条件 currentPage为当前页 pageSize为每页显示条数
SELECT TOP 23 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 23 id FROM TestTable ORDER BY id)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID
SELECT * FROM (
SELECT *
from (
SELECT S.ID,S.CONTRACT,S.ERATE,S.INFORMATIONFEE,S.PLACE,S.MEMO,S.INPUTDATE,rownum rownum_
FROM NEW5I5JDBA.CONSUME S
WHERE 1=1 AND S.COMPANYSID = 1 and S.contract like '%M%'
order by S.ID ASC
) row_
WHERE rownum_<=23+15
) WHERE rownum_ >=16
另外,描红部分会降低查询效率的。当数据量大时会有所凸显。
23+15 我也没看懂···
( select x.*, rownum rnum from
(
SELECT S.ID,S.CONTRACT,S.ERATE,S.INFORMATIONFEE,S.PLACE,S.MEMO,S.INPUTDATE
FROM NEW5I5JDBA.CONSUME S WHERE 1=1
AND S.COMPANYSID = 1 and S.contract like '%M%'
order by S.ID ASC
) x where rownum <= 23*16
) y where rnum > 23*15 ;