SQL的各种常用算法问题

---------------------------------------------
分页算法:
---------------------------------------------
实现分页，可以使用嵌套查询，也可以使用存储过程。不过存储过程太复杂，执行效率也不一定高，
所以我就用这样的嵌套语句来实现：

SELECT TOP 页大小 * FROM TestTable
WHERE (ID >(SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id  FROM TestTable ORDER BY id) AS T)) ORDER BY ID


select * from (select * from scott.emp order by scott.emp.empno) where rownum<=20;

select a.empno from (select * from scott.emp where rownum<=20 order by scott.emp.empno ) a
minus
select b.empno from (select * from scott.emp where rownum<=10 order by scott.emp.empno) b ;


select a.empno from (select * from scott.emp where rownum<=20 order by scott.emp.empno ) a
union all
select b.empno from (select * from scott.emp where rownum<=10 order by scott.emp.empno) b ;

select a.empno from (select * from scott.emp where rownum<=20 order by scott.emp.empno) a
minus
select b.empno from scott.emp b

select a.empno from (select * from scott.emp where rownum<=20 order by scott.emp.empno) a
minus
select b.empno from scott.emp b  where rownum<=10 ;

select a.empno from (select * from scott.emp where rownum<=20 order by scott.emp.empno) a
intersect
select b.empno from (select * from scott.emp where rownum<=10 order by scott.emp.empno) b ;


select aa.empno from ( select * from (select * from scott.emp order by scott.emp.empno) a where rownum<=20 ) aa
minus
select bb.empno from ( select * from (select * from scott.emp order by scott.emp.empno) a where rownum<=10 ) bb



select a.empno from (select scott.emp.empno from scott.emp where rownum<=20 order by scott.emp.empno) a
minus
select b.empno from (select scott.emp.empno from scott.emp where rownum<=10 order by scott.emp.empno) b;