向下递归
WITH RECURSIVE down(id,name,pid,layer,sort,type_id,is_piece,piece_Id,type_tree_id,group_id,ref01,ref02,ref03,ref04) AS
( SELECT id,ref04 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,a.ref01,a.ref02,a.ref03,a.ref04 FROM
t_product_tree a,down b WHERE b.id = a.pid
) SELECT * from down
向上递归
WITH RECURSIVE up(id,ref04) AS
( select id,ref04 from t_product_tree
where id = '2014112411014156812'
UNION SELECT m.id,m.name,m.pid,m.layer,m.sort,m.type_id,m.is_piece,m.piece_Id,m.type_tree_id,m.group_id,m.ref01,m.ref02,m.ref03,m.ref04
FROM t_product_tree m,up n WHERE n.pid = m.id
) SELECT * from up
limit N表示取N个记录.
offset P表示从下标P开始,第一行的下标是0,也可以理解为跳过P行.
limit要放在查询语句的最后:
select * from finery where id > 7 order by id limit 10 offset 0;
语法参考sqlite官网：http://www.sqlite.org/lang_with.html 原文链接：https://www.f2er.com/sqlite/199777.html