如何使用 CTE 对相邻列表的父母和所有兄弟姐妹进行排序? [英] How to sort parents and all siblings using CTE for adjacency list?

问题描述

这是我的桌子:

CREATE TABLE IF NOT EXISTS NODE 
(
    UUID VARCHAR NOT NULL, 
    PARENT_UUID VARCHAR NULL,
    NAME VARCHAR NOT NULL, 
    PRIMARY KEY (UUID)
);

这是我的测试数据:

INSERT INTO node (uuid, parent_uuid, name)
VALUES 
('dfca05bc-551d-4e3d-87aa-7dd7d29539f6', null, 'Computers'),
('ff83eb99-ea2c-4d11-8ebe-4600445a3bda', null, 'Food'),
('405f0267-fa22-4cac-a397-c430be221828', null, 'Drinks'),
('e79ecefa-c3e2-400f-aab4-2d28fcd3a832', 'dfca05bc-551d-4e3d-87aa-7dd7d29539f6', 'Monitors'),
('61a94b77-56c2-48ff-b869-39305648a25c', 'dfca05bc-551d-4e3d-87aa-7dd7d29539f6', 'System blocks'),
('5d88a9b7-7ffe-45e5-b35f-9350072ed619', 'dfca05bc-551d-4e3d-87aa-7dd7d29539f6', 'Mother boards'),
('5994c39d-c4ea-454c-ae57-118392b93f66', 'dfca05bc-551d-4e3d-87aa-7dd7d29539f6', 'Different'),
('d1c994fe-f3ec-40ed-aace-5221c026c0ea', '5994c39d-c4ea-454c-ae57-118392b93f66', 'Keyboards'),
('7757aa9b-abee-4d30-89d3-79f77613b5e8', '5994c39d-c4ea-454c-ae57-118392b93f66', 'Mice'),
('33d93c3a-1c2d-44b9-8fac-3f83074104a5', '5994c39d-c4ea-454c-ae57-118392b93f66', 'Joysticks'),
('f13bb023-47b2-473a-83b0-4223ff6e28b9', 'e79ecefa-c3e2-400f-aab4-2d28fcd3a832', 'Size 14'),
('312a4e56-71ef-4372-a556-17ace15197e6', 'e79ecefa-c3e2-400f-aab4-2d28fcd3a832', 'Size 15'),
('c525374c-6a06-46e4-98c8-bc669e811e22', 'e79ecefa-c3e2-400f-aab4-2d28fcd3a832', 'Size 16');

我想得到 a) 所有父母和他们的兄弟姐妹 b) 节点的所有兄弟姐妹.例如，如果我选择了 Joysticks 节点，那么我想获得

I want to get a) all parents and their siblings b) all the siblings of the node. For example, if I have Joysticks node is selected, then I want to get

• 计算机
  • 不同
    • 操纵杆
    • 键盘
    • 小鼠

    如您所见，未选择属于监视器的大小 14、15、16 节点.

    As you see Size 14, 15, 16 nodes that belong to monitors are not selected.

    到目前为止，我有以下查询:

    Up to now I have the following query:

    WITH RECURSIVE theparents (uuid, parent_uuid, name, level) AS (
            SELECT uuid, parent_uuid, name, 0 AS level, 
            FROM node 
            WHERE uuid = '33d93c3a-1c2d-44b9-8fac-3f83074104a5'
    
            UNION ALL
    
            SELECT a.uuid, a.parent_uuid, a.name, b.level + 1 as level
            FROM node a
            INNER JOIN theparents b ON b.parent_uuid = a.uuid
        ),
    thesiblings AS (
        SELECT a.uuid, a.parent_uuid, a.name, b.level as level
        FROM node a
        INNER JOIN theparents b ON b.parent_uuid = a.parent_uuid OR (b.parent_uuid IS NULL AND a.parent_uuid IS NULL)
    )
    SELECT * FROM thesiblings;
    

    此查询选择所有节点，但不进行排序.是否可以使用 sql 对它们进行排序，或者只能手动完成?

    This query selects all nodes, but without sorting. Is it possible to sort them using sql or it can be done only manually?

    推荐答案

    你可以将 tree 加入到 node.唯一的问题是保持它根据树遍历顺序排序.试试这个，在 MySql 8.0 中测试

    You can just join the tree to node. The only problem is to keep it sorted according to tree traversal order. Try this, tested in MySql 8.0

    编辑

    现在按基于名称的路径排序，20 是表中的最大名称长度

    Now sorting by names-based path, 20 is max name length in the table

    WITH RECURSIVE tree (uuid, parent_uuid, name, level, path) AS 
    (
        SELECT uuid, parent_uuid, name, 0 level, cast(Rpad(name, 20, ' ') as char(200)) path
        FROM nodes 
        WHERE uuid = '33d93c3a-1c2d-44b9-8fac-3f83074104a5'
    
        UNION ALL
    
        SELECT a.uuid, a.parent_uuid, a.name, level-1,  concat(Rpad(a.name, 20, ' '),'>', path)
        FROM nodes a
        INNER JOIN tree b ON b.parent_uuid = a.uuid
    )
    select uuid, name, level /*, path */
    from (
       select n.uuid, n.name
          , max(-level) over() + level + 1  level
          , concat(substring(first_value(path) over(order by level), 1, (20+1) * (max(-level) over() + level +1 )), n.name) path
       from tree t
       join nodes n on n.parent_uuid = t.uuid 
           -- no children for starting node
           and t.level <> 0
       
       union all
       --  roots
       select uuid, name, 0, Rpad(name, 20, ' ')
       from nodes
       where parent_uuid is null 
    ) t
    order by path
    

    db<>fiddle

    这篇关于如何使用 CTE 对相邻列表的父母和所有兄弟姐妹进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！