Sqlite3递归查询,分页

发布时间：2020-12-12 19:47:25 所属栏目：百科来源：网络整理

导读：使用Sqlite3实现递归查询向下递归 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,

使用Sqlite3实现递归查询

向下递归

 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

Sqlite的分页查询: 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!