如何使递归查询的SQLite的？ [英] How to make recursive query in SQLite?

问题描述

如果我的数据结构是这样的

  parentA
------- parentAA
-------------- parentAAA
--------------------- childA
 

如果我能得到childA.name。我怎么能知道所有的父母的名字，直至顶层。 所以这将是这样的> parentA / parentAA / parentAAA / childA

什么是做到这一点的最好方法是什么？

我在高级工作使用SQLite和JAVA /机器人..感谢。

__ _ __ _ __ _ 的___ 修改

好，大家好，感谢所有的U。所以我只是重复选择查询做到这一点。 BOTTOM-UP这是我创建的方法

 公共字符串getPath（INT ID，整型）{
        StringBuilder的pathBuilder =新的StringBuilder（）;
        字符串SQL = NULL;
        INT parentId的= 0;

        如果（ID == 0）{
            pathBuilder.insert（0，/根/）;
            返回pathBuilder.toString（）;
        }

        如果（类型== LayerManagementActivity.PARENT）{
            做 {
                SQL =SELECT ID，名称，PARENT_ID从parents_table其中id =
                        + ID;
                光标C = mDatabase.rawQuery（SQL，NULL）;
                如果（c.moveToFirst（））{
                    parentId的= c.getInt（2）;
                    的id = c.getInt（0）;
                    pathBuilder.insert（0，/+ c.getString（1））;
                    c.close（）;
                }
                ID = parentId的;
            }而（parentId的！= 0）;

            pathBuilder.insert（0/根）;
            pathBuilder.append（/）;

        }否则，如果（类型== LayerManagementActivity.CHILD）{
            SQL =选择编号，姓名，folder_id从childs_table WHERE ID =+ ID;
            光标C = mDatabase.rawQuery（SQL，NULL）;
            如果（c.moveToFirst（））{
                pathBuilder.append（c.getString（1））;
                的id = c.getInt（0）;
                INT folderId = c.getInt（2）;
                字符串路径= getPath（folderId，LayerManagementActivity.PARENT）;
                pathBuilder.insert（0，路径）;
            }
            c.close（）;
        }
        Log.d（碎屑，pathBuilder.toString（））;
        返回pathBuilder.toString（）;
    }
 

解决方案

SQLite不支持递归的热膨胀系数（CTE的或在所有与此有关）

没有与SQLite中。因为你不知道有多深不言而喻，你不能使用标准的JOIN招假递归CTE。你必须做硬盘的方式，并在客户端code实现递归：

• 抓斗的初始行和子部分的ID。
• 抓斗的行和子部分的ID为子部件。
• 在重复，直到没有回来。

if my data structure is like this

parentA
-------parentAA
--------------parentAAA
---------------------childA

if i can get "childA.name" . how can i know all the parent name till the top level. so it will be like this > parentA/parentAA/parentAAA/childA

what is the best way to do this ?

i'm working with SQLite and JAVA/android .. thanks in adv.

____________ EDIT

Okay guys, thanks for all of u . so i just make it by repeating "select query". BOTTOM-UP this is the method i create

public String getPath(int id, int type) {
        StringBuilder pathBuilder = new StringBuilder();
        String sql = null;
        int parentId = 0;

        if (id == 0) {
            pathBuilder.insert(0, "/root/");
            return pathBuilder.toString();
        }

        if (type == LayerManagementActivity.PARENT) {
            do {
                sql = "SELECT id, name, parent_id from parents_table where id="
                        + id;
                Cursor c = mDatabase.rawQuery(sql, null);
                if (c.moveToFirst()) {
                    parentId = c.getInt(2);
                    id = c.getInt(0);
                    pathBuilder.insert(0, "/" + c.getString(1));
                    c.close();
                }
                id = parentId;
            } while (parentId != 0);

            pathBuilder.insert(0, "/root");
            pathBuilder.append("/");

        } else if (type == LayerManagementActivity.CHILD) {
            sql = "SELECT id, name, folder_id FROM childs_table WHERE id=" + id;
            Cursor c = mDatabase.rawQuery(sql, null);
            if (c.moveToFirst()) {
                pathBuilder.append(c.getString(1));
                id = c.getInt(0);
                int folderId = c.getInt(2);
                String path = getPath(folderId, LayerManagementActivity.PARENT);
                pathBuilder.insert(0, path);
            }
            c.close();
        }
        Log.d("crumb", pathBuilder.toString());
        return pathBuilder.toString();
    }

解决方案

SQLite doesn't support recursive CTEs (or CTEs at all for that matter),

there is no WITH in SQLite. Since you don't know how deep it goes, you can't use the standard JOIN trick to fake the recursive CTE. You have to do it the hard way and implement the recursion in your client code:

• Grab the initial row and the sub-part IDs.
• Grab the rows and sub-part IDs for the sub-parts.
• Repeat until nothing comes back.

这篇关于如何使递归查询的SQLite的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！