时间戳

首页 > 解决方案 > 如果 parent_id 已经存在，如何查找和删除 child_id

sql - 如果 parent_id 已经存在，如何查找和删除 child_id

问题描述

我在父子和扁平结构中都有 2 个层次结构数据表

父子表：

PARENT_ID   CHILD_ID 
1234        1111 
1234        2222 
1234        3333 
3333        33XX 
3333        33YY

扁平结构：

LEVEL_1 LEVEL_2 LEVEL_3
1234    1111    
1234    2222    
1234    3333    33XX
1234    3333    33YY

我有另一个用户表，其中包含哪个用户可以访问哪个 ID

USER_ID   USER_ACCESS
USER_A    1234,1111 
USER_B    33YY,3333,1234

我已将上表进一步转换为以下格式

USER_ID   USER_ACCESS
USER_A    1234
USER_A    1111 
USER_B    33YY
USER_B    3333
USER_B    1234

如果他的 ID 已经存在 parent_id，我需要为每个用户查找并删除 child_id

上述示例输出

USER_ID   USER_ACCESS
USER_A    1234
USER_B    1234

在 Oracle/SQL Server 中需要一个解决方案，避免任何循环。

标签： sqlsql-serveroracle

解决方案

先说几点：

拥有层次结构已经使对扁平结构的需求过时了。此外，扁平表永远无法容纳层次结构的所有级别。您添加的每个级别都需要您添加一个额外的列，这很可笑。

以您需要用逗号分隔键的方式存储用户访问权限，使您没有（好的）方法来验证表之间的约束。

话虽这么说（并使用不需要字符串拆分的表）：


DROP TABLE IF EXISTS #struct_info;
CREATE TABLE #struct_info (
  PARENT_ID VARCHAR(50),
  CHILD_ID VARCHAR(50)
);

DROP TABLE IF EXISTS #access_info;
CREATE TABLE #access_info (
  USER_ID VARCHAR(50),
  USER_ACCESS VARCHAR(50)
);

INSERT INTO #struct_info
(
  PARENT_ID,
  CHILD_ID
)
VALUES
('1234','1111'),
('1234','2222'),
('1234','3333'),
('3333','33XX'),
('3333','33YY')

INSERT INTO #access_info
(
  USER_ID,
  USER_ACCESS
)
VALUES
('USER_A','1234'),
('USER_A','1111'),
('USER_B','33YY'),
('USER_B','3333'),
('USER_B','1234')


SELECT * FROM #struct_info
SELECT * FROM #access_info

;WITH cte AS (
  SELECT 
    PARENT_ID,
    CHILD_ID
  FROM #struct_info
  UNION ALL
  SELECT
    cte.PARENT_ID,
    si.CHILD_ID
  FROM cte
  INNER JOIN #struct_info si
    ON si.PARENT_ID = cte.CHILD_ID
)
DELETE ai
FROM #access_info ai
WHERE EXISTS (
     SELECT 1
     FROM cte 
     WHERE cte.CHILD_ID = ai.USER_ACCESS
       AND EXISTS (SELECT 1 
                   FROM #access_info ai2 
                   WHERE ai2.USER_ID = ai.USER_ID
                     AND ai2.USER_ACCESS =  cte.PARENT_ID)
  )

SELECT * FROM #access_info

cte 确定所有可能的层次结构变体 delete 查找可以在该变体中找到父级的任何 child_id

推荐阅读