sql - 如何使 SQL 根据具有相同标签的项目创建标签层次结构？

Question

在我的系统中，我的每个项目都有一些标签。现在我想向用户展示一棵带有标签的树。树应该具有第一级的所有标签。
在每个一级标签内，应再次出现在也属于一级标签的项目中找到的所有标签。
在第三层，应该出现属于第一层和第二层的标签，以此类推。
这个想法是，用户可以根据他的标签过滤项目，并且可以改进进入树中的过滤，而无需键入标签。

项目最初作为字符串字段存储在项目表中，但为了简化此解决方案，我已将它们移至子表。

使用这些项目作为输入:

|Item       |Tags    |
|-----------|--------|
|Computer   |a,b,c   |
|Mouse      |a,c     |
|Keyboard   |c,d     |
|Monitor    |a,b     |

应该输出这棵树:

  Tree             Items that are show when selected

  root             Computer,Mouse,Keyboard,Monitor
    +--a           Computer,Mouse,Monitor
    |  +--b        Computer,Monitor
    |  |  +--c     Computer
    |  +--c        Computer,Mouse
    |     +--b     Computer
    +--b           Computer,Monitor
    |  +--a        Computer,Monitor
    |  |  +--c     Computer
    |  +--c        Computer
    |     +--a     Computer
    +--c           Computer,Mouse,Keyboard
    |  +--a        Computer,Mouse
    |  |  +--b     Computer
    |  +--b        Computer
    |  |  +--a     Computer
    |  +--d        Keyboard
    +--d           Keyboard
       +--c        Keyboard

此 SQL 应仅在 Oracle 数据库中运行，因此可以接受 connect by 运算符的使用。

我从 Delicious 的 Firefox 扩展中选择了这种行为，它以树的形式显示标签，但将其限制为只有两个级别，我需要它显示尽可能多的级别。

你有什么想法吗？

提前致谢。

Answer 1

[请原谅我的 SQL Server 语法...我的 Oracle 技能生疏而且我手头没有服务器]

在过去的十年里，我遇到过两次这个问题!

我的解决方案:

1. 设计具有递归关系的单表

   CREATE TABLE dummy(
     id        VARCHAR(128) NOT NULL,
     parent_id VARCHAR(128) NULL,
     --
     CONSTRAINT pk_dummy PRIMARY KEY(id),
     --
     CONSTRAINT fk_dummy_X_dummy
     FOREIGN KEY(id) REFERENCES dummy(id)
   )
   

2. 为预先计算层次结构设计反规范化表:

   CREATE TABLE dummy_hierarchy(
     id        VARCHAR(128) NOT NULL,
     parent_id VARCHAR(128) NOT NULL,
     depth     INT NOT NULL
   )
   

   dummy_hierarchy 具有以下属性:

   • id 与 depth 值为 '1' 有 self 关系
   • id 与其父级有关系，depth 值为“2”
   • id 与其祖 parent 有关系，depth 值为“3”
   • 等等...

   还有:

   • 从 id 中检索所有前辈，包括 id 本身
   • 从 parent_id 中检索所有后继者，包括 parent_id 本身

3. 在 dummy 上定义一个触发器，它将使 dummy_hierarchy 保持最新:

   CREATE TRIGGER tr_dummy_ins_upd_del
   ON dbo.dummy FOR INSERT, UPDATE, DELETE
   AS
   BEGIN
     DELETE dummy
     FROM DELETED
     WHERE dummy.id = DELETED.id
   
     INSERT INTO dbo.dummy_hierarchy(
       id, parent_id, depth
     )
     SELECT id, id, 1
     FROM INSERTED
   
     WHILE 1 = 1
     BEGIN
       INSERT INTO dbo.dummy_hierarchy(
         id, parent_id, depth
       )
       SELECT hie.id, par.parent_id, hie.depth + 1
       FROM
         INSERTED ins
           INNER JOIN(
             dbo.dummy_hierarchy hie
               INNER JOIN dummy par
               ON par.id = hie.parent_id
           )
           ON hie.id = ins.id
       WHERE par.parent_id IS NOT NULL
         --
         AND NOT EXISTS(
               SELECT id
               FROM dummy_hierarchy hie_par
               WHERE hie_par.id        = hie.id
                 AND hie_par.parent_id = par.parent_id
             )
   
       IF @@ROWCOUNT = 0
       BEGIN
         BREAK
       END
     END
   END
   

   作为 POC 数据:

   INSERT INTO dummy(id, parent_id) VALUES('COMPUTER', NULL)
   INSERT INTO dummy(id, parent_id) VALUES('MONITOR',  'COMPUTER')
   INSERT INTO dummy(id, parent_id) VALUES('MOUSE',    'MONITOR')
   INSERT INTO dummy(id, parent_id) VALUES('KEYBOARD', 'MONITOR')
   

   查询:

   SELECT *
   FROM dbo.dummy_hierarchy hie
   WHERE parent_id = 'COMPUTER'
   

   产量:

       id        parent_id depth    COMPUTER  COMPUTER  1    MONITOR   COMPUTER  2    MOUSE     COMPUTER  3    KEYBOARD  COMPUTER  3

   This one:

   SELECT *
   FROM dbo.dummy_hierarchy hie
   WHERE parent_id = 'MONITOR'
   

   产量:

       id        parent_id depth    MONITOR   MONITOR   1    MOUSE     MONITOR   2    KEYBOARD  MONITOR   2

   And, for back-tracking:

   SELECT *
   FROM dbo.dummy_hierarchy
   WHERE id = 'MOUSE'
   

   有:

       id        parent_id depth    MOUSE     MOUSE     1    MOUSE     MONITOR   2    MOUSE     COMPUTER  3

   当然，这并不是您所需要的。

   但我希望提供一些有用的线索。