android sqlite递归,Sqlite3递归查询,分页查询

版权声明

本文首发自优快云博客：http://blog.youkuaiyun.com/sisyphus_z；

作者：alex_zuo

无需授权即可转载，但请自觉保留以上版权声明。

技术点：使用Sqlite3实现递归查询，分页查询

向下递归

向上递归

Sqlite的分页查询

limit N表示取N个记录.

offset P表示从下标P开始, 第一行的下标是0, 也可以理解为跳过P行.

limit要放在查询语句的最后:

select * from finery where id > 7 order by id limit 10 offset 0;

递归查询可复制sql：

WITH RECURSIVE down(id,name,pid,layer,sort,type_id,is_piece,piece_Id,type_tree_id,group_id) AS

(

SELECT id,name,pid,layer,sort,type_id,is_piece,piece_Id,type_tree_id,group_id

FROM t_product_tree

WHERE id='2014112411014156812'

UNION

SELECT a.id,a.name,a.pid,a.layer,a.sort,a.type_id,a.is_piece,a.piece_Id,a.type_tree_id,a.group_id

FROM t_product_tree a,down b

WHERE b.id = a.pid

) SELECT * from down

WITH RECURSIVE up(id,name,pid,layer,sort,type_id,is_piece,piece_Id,type_tree_id,group_id) AS

(

SELECT id,name,pid,layer,sort,type_id,is_piece,piece_Id,type_tree_id,group_id

FROM t_product_tree

WHERE id='2014112411014156812'

UNION

SELECT a.id,a.name,a.pid,a.layer,a.sort,a.type_id,a.is_piece,a.piece_Id,a.type_tree_id,a.group_id

FROM t_product_tree a,up b

WHERE b.pid = a.id

) SELECT * from up