公司无聊的情况下,总结下这段时间面试中遇到到一些问题,先来说数据的分页查询:
oracle分页
SELECT * FROM (
SELECT A.*, ROWNUM, RN FROM (
SELECT * FROM TABLE ) A
WHERE ROWNUM <= 40 )
WHERE RN >= 20
mysql 分页
1、一般最基本的分页方式:
SELECT * FROM TABLE ORDER BY ID DESC LIMIT 0, 10
在中小型的数据访问中足够使用,但是如果海量数据的话,会有问题,比如超过1000000条数据的话,语句就回变为:
SELECT * FROM TABLE ORDER BY ID DESC LIMIT 100000000, 10;
就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。
2、使用子查询的方式来提高分页查询效率
SELECT * FROM TABLE WHERE id <=
(SELECT id FROM TABLE ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1)
ORDER BY id desc LIMIT $pagesize
3、使用JOIN
SELECT * FROM TABLE AS t1 JOIN
(SELECT id FROM TABLE ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;
都是网络上的资源,搜搜会找到,总结下,找着方便
oracle分页
SELECT * FROM (
SELECT A.*, ROWNUM, RN FROM (
SELECT * FROM TABLE ) A
WHERE ROWNUM <= 40 )
WHERE RN >= 20
mysql 分页
1、一般最基本的分页方式:
SELECT * FROM TABLE ORDER BY ID DESC LIMIT 0, 10
在中小型的数据访问中足够使用,但是如果海量数据的话,会有问题,比如超过1000000条数据的话,语句就回变为:
SELECT * FROM TABLE ORDER BY ID DESC LIMIT 100000000, 10;
就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。
2、使用子查询的方式来提高分页查询效率
SELECT * FROM TABLE WHERE id <=
(SELECT id FROM TABLE ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1)
ORDER BY id desc LIMIT $pagesize
3、使用JOIN
SELECT * FROM TABLE AS t1 JOIN
(SELECT id FROM TABLE ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;
都是网络上的资源,搜搜会找到,总结下,找着方便