sql - 如何在大查询中显示父子关系的完整层次结构
问题描述
一些背景---我有两张表一张表列出系统中的所有实体,另一张指定实体之间的关系
询问——询问是查看表格,我们可以绘制出每个子实体与父实体的关系。
- 我做了什么
CREATE TEMP TABLE rell AS
SELECT 3 child_id, 2 parent_id UNION ALL
SELECT 2, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 14, 6 UNION ALL
SELECT 15, 14 UNION ALL
SELECT 7, 8 UNION ALL
SELECT 8, 5 UNION ALL
SELECT 9, 10 UNION ALL
SELECT 11, 12 ;
CREATE TEMP TABLE mapp AS
SELECT 1 item_id, 'app' type UNION ALL
SELECT 2 , 'ci' UNION ALL
SELECT 3 , 'ci' UNION ALL
SELECT 4 , 'ci' UNION ALL
SELECT 5 , 'app' UNION ALL
SELECT 6 , 'ci' UNION ALL
SELECT 7 , 'ci' UNION ALL
SELECT 8 , 'ci' UNION ALL
SELECT 9 , 'app' UNION ALL
SELECT 10 , 'ci' UNION ALL
SELECT 11 , 'ci' UNION ALL
SELECT 14 , 'ci' UNION ALL
SELECT 15 , 'ci' UNION ALL
SELECT 12 , 'ci' ;
上面的清单 'mapp' 包含所有实体(类型 - app 是最终父级),并且 rel 表具有关系。
我可以得到类似下面的输出吗
original_child final_parent path
4 1 4>1
3 1 3>2>1
7 5 7>8>5
14 1 14>6>2>1
15 1 15>14>6>2>1
11 12 11>12
2 1 2>1
8 5 8>5
6 1 6>2>1
解决方案
好的,所以经过大量搜索互联网并在这里尝试多种选择后,我想出了很多时间来了解细节,但我想我已经找到了解决方案。也许它会为人们省去我遇到的麻烦。我会尽量解释
-- Initialise variables
DECLARE steps INT64 DEFAULT 1;
DECLARE table_holder ARRAY<STRUCT<original_child INT64, latest_parent INT64,path STRING>>;
--- Set up dummy tables
CREATE TEMP TABLE rell AS
SELECT 3 child_id, 2 parent_id UNION ALL
SELECT 2, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 14, 6 UNION ALL
SELECT 15, 14 UNION ALL
SELECT 7, 8 UNION ALL
SELECT 8, 5 UNION ALL
SELECT 9, 10 UNION ALL
SELECT 11, 12 ;
CREATE TEMP TABLE mapp AS
SELECT 1 item_id, 'app' type UNION ALL
SELECT 2 , 'ci' UNION ALL
SELECT 3 , 'ci' UNION ALL
SELECT 4 , 'ci' UNION ALL
SELECT 5 , 'app' UNION ALL
SELECT 6 , 'ci' UNION ALL
SELECT 7 , 'ci' UNION ALL
SELECT 8 , 'ci' UNION ALL
SELECT 9 , 'app' UNION ALL
SELECT 10 , 'ci' UNION ALL
SELECT 11 , 'ci' UNION ALL
SELECT 14 , 'ci' UNION ALL
SELECT 15 , 'ci' UNION ALL
SELECT 12 , 'ci' ;
SET table_holder = (
SELECT ARRAY_AGG(STRUCT(a.item_id,
b.parent_id, CONCAT(CAST(a.item_id AS STRING),">",CAST(b.parent_id AS STRING)))
) cls from mapp a inner join rell b on a.item_id = b.child_id where a.type!='app') ;
LOOP
SET table_holder = (
SELECT ARRAY_AGG(STRUCT(a.original_child,
coalesce(b.parent_id,a.latest_parent), coalesce( CONCAT(path,">",CAST(b.parent_id AS STRING)),path))
) cls from UNNEST (table_holder) a left outer join rell b on a.latest_parent = b.child_id ) ;
SET steps = steps+1;
IF steps=5 THEN LEAVE; END IF;
END LOOP;
SELECT * from UNNEST (table_holder);
数组和结构已被使用,因为它们更易于使用。并且 bigquery 脚本已用于循环。如果人们期望许多级别,则可以增加失控条件。
这是最终输出
original_child final_parent path
4 1 4>1
3 1 3>2>1
7 5 7>8>5
14 1 14>6>2>1
15 1 15>14>6>2>1
11 12 11>12
2 1 2>1
8 5 8>5
6 1 6>2>1
希望它可以帮助其他人进行类似的练习。
推荐阅读
- rabbitmq - 如何使用 RabbitMq Admin UI 记录队列
- excel - 无法写入单元格。始终运行时错误“1004”
- c# - Automapper:ReverseMap 的最佳实践
- bash - 重新启动期间未捕获 SIGTERM
- java - 使用 URLConnection - 在读取期间获取连接超时 - 这怎么可能?
- javascript - 制表符和多维数组
- git - git fetch --all 与 git fetch -p | 有什么不同?
- python-3.x - 在列表列表中查找最频繁的元素/行
- typescript - TypeScript 确保所有情况都针对联合类型处理
- java - 匿名类重构问题