--分页显示商品id、商品名称、上架时间,每页显示4条,
--请编写查询第2页商品信息的sql语句
SELECT ID,NAME,saledate,ROWNUM FROM es_product
WHERE ROWNUM>=5 AND ROWNUM<=8;
--将rownum固化
SELECT ID,NAME,saledate,ROWNUM rn FROM es_product
--通过限制rn的范围取记录
SELECT id,name,saledate FROM (SELECT ID,NAME,saledate,ROWNUM rn
FROM es_product)
WHERE rn>=5 AND rn<=8
SELECT id,name,saledate FROM (SELECT ID,NAME,saledate,ROWNUM rn
FROM es_product) a
WHERE a.rn>=5 AND a.rn<=8
--按日期降序,分页显示商品id、商品名称、上架时间,每页显示4条,
--请编写查询第2页商品信息的sql语句
SELECT ID, NAME, SALEDATE
FROM (SELECT ID, NAME, SALEDATE, ROWNUM RN
FROM (SELECT * FROM ES_PRODUCT ORDER BY SALEDATE DESC)) A
WHERE A.RN >= 5
AND A.RN <= 8
下面是另外一个表的查询 方式
分页不排序:
select*from (SELECT t.*,rownum rn FROM productinfo t) a
WHERE a.rn <=10
and
a.rn >= 6
分页有排序:
select*from (SELECT t.*,rownum rn FROM (select*from productinfo
order by quantity desc) t) a
WHERE a.rn <=10
and
a.rn >= 6