sql server方案1:
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
sql server方案2:
select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
mysql方案:select * from t order by id limit 30,10
oracle方案:select * from (select rownum r,* from t where r<=40) where r>30
分页:
mysql:sql = "select * from articles limit " + (pageNo-1)*pageSize + "," + pageSize;
oracle: sql = "select * from " +
"(select rownum r,* from " +
"(select * from articles order by postime desc)" +
"where rownum<= " + pageNo*pageSize +") tmp " +
"where r>" + (pageNo-1)*pageSize;
SELECT
*
FROM
(
SELECT
tmp_page.*, ROWNUM row_id
FROM
(
SELECT
*
FROM
T_APSG_CHECKRECORD T
WHERE
1 = 1
AND "orgCanOrg" (ORG_ID, '1f65a8afc7a44087be6e0148e34faaeb') = 1
ORDER BY
create_time DESC
) tmp_page
WHERE
ROWNUM <= '17920'
)
WHERE
row_id > '17910'
注释:第7行保证rownum的顺序是确定的,因为oracle的索引会造成rownum返回不同的值
简洋提示:没有order by时,rownum按顺序输出,一旦有了order by,rownum不按顺序输出了,这说明rownum是排序前的编号。如果对order by从句中的字段建立了索引,那么,rownum也是按顺序输出的,因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。
sqlserver:sql = "select top 10 * from articles where id not in(select top " + (pageNo-1)*pageSize + "id from articles)"