SQLite的：与克劳斯结果怪异 [英] SQLite : with clouse result weird

问题描述

现在我可以在我的Andr​​oid应用程序中实现sqlite3的3.8.4.3，我发现一个真棒库名sqlcipher，：）

现在这里再次声明，我想要一个Android应用程序中实现递归函数;

我尝试（递归函数）从CMD窗口：

我创建一个表：

  CREATE TABLE树（
 id_tree整数PRIMARY KEY AUTOINCREMENT，
 id_boss TEXT，
 id_child TEXT，
 ANSW TEXT）;
 

插入一些值：

  INSERT INTO树（id_boss，id_child，ANSW）VALUES（'1'，'2'，'T'）;
 INSERT INTO树（id_boss，id_child，ANSW）VALUES（'1'，'3'，'F'）;
 INSERT INTO树（id_boss，id_child，ANSW）VALUES（'2'，'P1'，'T'）;
 INSERT INTO树（id_boss，id_child，ANSW）VALUES（'2'，'4'，'F'）;
 INSERT INTO树（id_boss，id_child，ANSW）VALUES（'3'，'P2'，'T'）;
 INSERT INTO树（id_boss，id_child，ANSW）VALUES（'3'，'8'，'F'）;
 

然后我运行一个递归查询，现在速效SQLite的3.8：

  WITH RECURSIVE
 under_alice（名称，等级）AS（
   VALUES（'1'，'0'）
   UNION ALL
   SELECT tree.id_child，under_alice.level + 1
     从一棵树，under_alice
    WHERE tree.id_boss = under_alice.name
    ORDER BY 2 DESC
    ）
   SELECT SUBSTR（'..........'，1，水平* 3）||名字从under_alice;
 

怪异的结果：

  1
  2 ...
  ...... 4
  ...... P1
  ... 3
  ...... 8
  P2 ......
 

为什么树的端部被选择的号码2的行值各组？
如何使查询所以结果应该这样吗？

  1
  2 ...
  ...... P1
  ...... 4
  ... 3
  P2 ......
  ...... 8
 

解决方案

此查询仅由第2列排序记录的水平。

要在同一级别指定记录的顺序，你必须到相应的列添加到ORDER BY子句。
在这种情况下，这可能是 id_tree 价值，你必须添加到SELECT子句，使其可用：

  WITH RECURSIVE
under_alice（姓名，水平，order_nr）AS（
  VALUES（'1'，'0'，0）
  UNION ALL
  SELECT tree.id_child，under_alice.level + 1，tree.id_tree
    从一棵树，under_alice
   WHERE tree.id_boss = under_alice.name
   ORDER BY 2 DESC，3
）
SELECT SUBSTR（'..........'，1，水平* 3）||名字从under_alice;
 

Now i can implement sqlite3 3.8.4.3 within my android app, i found an awesome library name sqlcipher , :)

and now here again i'm trying to implement recursive function within an android app;

i try (recursive function) from 'cmd' windows:

i create a table :

 CREATE TABLE tree(
 id_tree integer PRIMARY KEY AUTOINCREMENT,
 id_boss TEXT,
 id_child TEXT,
 answ TEXT);

insert some value :

 INSERT INTO tree(id_boss,id_child,answ) VALUES('1','2','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('1','3','F');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('2','P1','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('2','4','F');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('3','P2','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('3','8','F');

and then i running a recursive query that now availabe for sqlite 3.8 :

 WITH RECURSIVE
 under_alice(name,level) AS (
   VALUES('1','0')
   UNION ALL
   SELECT tree.id_child, under_alice.level+1
     FROM tree, under_alice
    WHERE tree.id_boss=under_alice.name 
    ORDER BY 2 DESC
    )
   SELECT  substr('..........',1,level*3) || name FROM under_alice;

the weird result :

  1
  ...2
  ......4
  ......P1
  ...3
  ......8
  ......P2

why the end of the tree is selected number 2 row value each group ? how to make a query so the result should like this ?

  1
  ...2
  ......P1
  ......4
  ...3
  ......P2
  ......8

解决方案

This query sorts the records only by the 2nd column, the level.

To specify the order of records at the same level, you have to add the appropriate column to the ORDER BY clause. In this case, this is probably the id_tree value, which you have to add to the SELECT clause so that it is available:

WITH RECURSIVE
under_alice(name,level,order_nr) AS (
  VALUES('1','0',0)
  UNION ALL
  SELECT tree.id_child, under_alice.level+1, tree.id_tree
    FROM tree, under_alice
   WHERE tree.id_boss=under_alice.name 
   ORDER BY 2 DESC, 3
)
SELECT substr('..........',1,level*3) || name FROM under_alice;

这篇关于SQLite的：与克劳斯结果怪异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！