sql - 对嵌套集中的 sibling 进行排序

Question

我在 SQLite 数据库中有一个嵌套的集合模型。 sibling 需要按字母顺序排序。下面是完整的集合，但需要排序的 sibling 是第二层。如您所见，它们按以下顺序开始:

Kanval, Wafiyah, Qamar, Lamya, Chaman, Fadila

familyName            | lft  | rgt
----------------------+------+-----
Families              |   1  |  62
-- Kanval             |   2  |   9
---- Omera            |   3  |   4
---- Dafiyah          |   5  |   6
---- Daneen           |   7  |   8
-- Qamar              |  10  |  19
---- Deeba            |  11  |  12
---- Pakeezah         |  13  |  14
---- Rabiya           |  15  |  16
---- Banafsha         |  17  |  18
-- Lamya              |  20  |  33
---- Banujah          |  21  |  22
---- Buthaynah        |  23  |  24
---- Vardah           |  25  |  26
---- Kaneez           |  27  |  28
---- Parveen          |  29  |  30
---- Ghunyah          |  31  |  32
-- Chaman             |  34  |  45
---- Kanz             |  35  |  36
---- Varisha          |  37  |  38
---- Kunza            |  39  |  40
---- Khusbakht        |  41  |  42
---- Ermina           |  43  |  44
-- Fadila             |  46  |  53
---- Tahani           |  47  |  48
---- Iffah            |  49  |  50
---- Huwaydah         |  51  |  52
-- Wafiyah            |  54  |  61
---- Asheeyana        |  55  |  56
---- Hutun            |  57  |  58
---- Aakifah          |  59  |  60

但是需要按照这样的顺序排序:

Chaman, Fadila, Kanval, Lamya, Qamar, Wafiyah

familyName        | lft | rgt
------------------+-----+-----
Families          |   1 |  62
--Chaman          |   2 |  13
----Kanz          |   3 |   4
----Varisha       |   5 |   6
----Kunza         |   7 |   8
----Khusbakht     |   9 |  10
----Ermina        |  11 |  12
--Fadila          |  14 |  21
----Tahani        |  15 |  16
----Iffah         |  17 |  18
----Huwaydah      |  19 |  20
--Kanval          |  22 |  29
----Omera         |  23 |  24
----Dafiyah       |  25 |  26
----Daneen        |  27 |  28
--Lamya           |  30 |  43
----Banujah       |  31 |  32
----Buthaynah     |  33 |  34
----Vardah        |  35 |  36
----Kaneez        |  37 |  38
----Parveen       |  39 |  40
----Ghunyah       |  41 |  42
--Qamar           |  44 |  53
----Deeba         |  45 |  46
----Pakeezah      |  47 |  48
----Rabiya        |  49 |  50
----Banafsha      |  51 |  52
--Wafiyah         |  54 |  61
----Asheeyana     |  55 |  56
----Hutun         |  57 |  58
----Aakifah       |  59 |  60

我有 Joe Celko 的 Trees and Hieracrchies in SQL for Smarties，它有一些移动节点的例子，比如将 Chaman 移动到前面。我也在网上找到了类似的例子，但是我找不到任何可以对所有 sibling 进行排序的 SQL 例子。

如何对 sibling 进行排序？

有关我如何创建上述数据的详细信息...

我有一个将填充嵌套集的测试应用程序。所以我只是简单地创建了两次，一次是乱序的名称，第二次是为了显示所需结果的名称。至于实际从数据库中获取这些数据，我使用了这个查询:

  SELECT COUNT(e1.ObjectId) AS LEVEL, e2.name, e2.lft, e2.rgt
    FROM EventNode AS e1, EventNode AS e2
   WHERE e2.lft BETWEEN e1.lft AND e1.rgt
GROUP BY e2.ObjectId
ORDER BY e2.lft  

顺序之所以重要，是因为顺序将由最终用户控制。他们将能够以任何一种方式排序，也可以移动各个节点，以便 sibling 按照他们想要的顺序显示。因此，树中数据的顺序正确很重要。

(P.S.在真实数据中有一个ObjectID，它是一个唯一的标识符，它允许名称在嵌套集中重复)

Answer 1

考虑到您的表具有以下结构:

EventNode(objectID, familyName, lft, rgt) 

我首先获取每个节点的 parentObjectID，然后使用所需的 lft 和 rgt 值重新生成嵌套集。我还按字母顺序对第 3 级的子级重新排序，并将示例数据插入到 EventNode 中:

CREATE TABLE EventNode 
(
    objectID INT IDENTITY(1, 1) NOT NULL, 
    familyName VARCHAR(20) NOT NULL,
    lft TINYINT NOT NULL,
    rgt TINYINT NOT NULL
)
GO

INSERT INTO EventNode (familyName, lft, rgt)
VALUES
('Families' ,    1  ,   62),
('Kanval'   ,    2  ,    9), 
('Omera'    ,    3  ,    4), 
('Dafiyah'  ,    5  ,    6), 
('Daneen'   ,    7  ,    8), 
('Qamar'    ,   10  ,   19), 
('Deeba'    ,   11  ,   12), 
('Pakeezah' ,   13  ,   14), 
('Rabiya'   ,   15  ,   16), 
('Banafsha' ,   17  ,   18), 
('Lamya'    ,   20  ,   33), 
('Banujah'  ,   21  ,   22), 
('Buthaynah',   23  ,   24), 
('Vardah'   ,   25  ,   26), 
('Kaneez'   ,   27  ,   28), 
('Parveen'  ,   29  ,   30), 
('Ghunyah'  ,   31  ,   32), 
('Chaman'   ,   34  ,   45), 
('Kanz'     ,   35  ,   36), 
('Varisha'  ,   37  ,   38), 
('Kunza'    ,   39  ,   40), 
('Khusbakht',   41  ,   42), 
('Ermina'   ,   43  ,   44), 
('Fadila'   ,   46  ,   53), 
('Tahani'   ,   47  ,   48), 
('Iffah'    ,   49  ,   50), 
('Huwaydah' ,   51  ,   52), 
('Wafiyah'  ,   54  ,   61), 
('Asheeyana',   55  ,   56), 
('Hutun'    ,   57  ,   58), 
('Aakifah'  ,   59  ,   60)

;WITH familyHierarchy(familyName, objectID, parentObjectID)
AS
(
    SELECT familyName, objectID, 
        (SELECT TOP 1 objectID
       FROM EventNode e2 
       WHERE e2.lft < e1.lft AND e2.rgt > e1.rgt    
       ORDER BY e2.rgt-e1.rgt ASC) AS parentObjectID
    FROM EventNode e1

)
, EventNodeRN
AS                              
(                                
    SELECT familyName, objectID, parentObjectID,                                  
    ROW_NUMBER() OVER(PARTITION BY parentObjectID ORDER BY familyName, objectID) * 2 - 1 AS n                                
    FROM familyHierarchy                          
)
, C1 
AS                              
(                                                    
     SELECT objectID, 1 AS arm, CAST(0x01 AS VARBINARY(8000)) AS sortpath                                
     FROM familyHierarchy
     WHERE parentObjectID is NULL                                                               
     UNION ALL                                                                                  
     SELECT objectID, 2 AS arm, CAST(0x02 AS VARBINARY(8000)) AS sortpath                                
     FROM familyHierarchy
     WHERE parentObjectID is NULL                                                               
     UNION ALL                                                               
     SELECT E.objectID, 1 AS arm,                                  
     CAST(M.sortpath + CAST(E.n AS BINARY(1)) AS VARBINARY(8000)) AS sortpath                                
     FROM C1 AS M                                  
     INNER JOIN EventNodeRN AS E                                    
     ON E.parentObjectID = M.objectID
     WHERE M.arm = 1                                                               
     UNION ALL                                                               
     SELECT E.objectID, 2 AS arm,                                   
     CAST(M.sortpath + CAST(E.n + 1 AS BINARY(1)) AS VARBINARY(8000)) AS sortpath                                
     FROM C1 AS M                                  
     INNER JOIN EventNodeRN AS E                                    
     ON E.parentObjectID = M.objectID
     WHERE M.arm = 1                              
)
, c2 
AS                              
(                                
    SELECT objectID, ROW_NUMBER() OVER(ORDER BY sortpath) AS sortval                                
    FROM C1                              
)

UPDATE e
SET lft = reordered.lft, rgt = reordered.rgt
FROM EventNode e     
INNER JOIN (
    SELECT c2.objectID, e.familyName, MIN(sortval) AS lft, MAX(sortval) AS rgt                              
    FROM c2   
    INNER JOIN familyHierarchy e ON c2.objectID = e.objectID
    GROUP BY c2.objectID, e.familyName
    ) reordered ON e.objectID = reordered.objectID

现在，查询初始表返回以下结果:

familyName  lft rgt
Families    1   62
Chaman      2   13
Ermina      3   4
Kanz        5   6
Khusbakht   7   8
Kunza       9   10
Varisha     11  12
Fadila      14  21
Huwaydah    15  16
Iffah       17  18
Tahani      19  20
Kanval      22  29
Dafiyah     23  24
Daneen      25  26
Omera       27  28
Lamya       30  43
Banujah     31  32
Buthaynah   33  34
Ghunyah     35  36
Kaneez      37  38
Parveen     39  40
Vardah      41  42
Qamar       44  53
Banafsha    45  46
Deeba       47  48
Pakeezah    49  50
Rabiya      51  52
Wafiyah     54  61
Aakifah     55  56
Asheeyana   57  58
Hutun       59  60