SQL挑战/拼图：如何使用SQL查询创建ASCII艺术层次结构树？ [英] SQL Challenge/Puzzle: How to create an ASCII art hierarchy tree with an SQL query?
本文介绍了SQL挑战/拼图：如何使用SQL查询创建ASCII艺术层次结构树？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
$ b $这个最初的动机是显示ORACLE的实际执行计划，保存在GV $ SQL_PLAN中。 b
- 我附上了我的建议解决方案。
- 只要符合要求，请随时添加。
- 请提及您的数据库名称解决方案是为。
要求
输入
- 列id（节点的id）和pid（节点的父节点id）。
输出
代码 b
- 仅基于本地SQL的单个SELECT语句$ UD $（用户定义的函数）
$ b- 没有T-SQL，PL / SQL等。
样本数据
create table h（id int，pid int）;
插入h（id，pid）值（0，null）;
插入h（id，pid）值（1,0）;
插入h（id，pid）值（2，1）;
插入h（id，pid）值（3，2）;
插入h（id，pid）值（4，3）;
插入h（id，pid）值（5，4）;
插入h（id，pid）值（6，3）;
插入h（id，pid）值（7，6）;
插入h（id，pid）值（8,7）;
插入h（id，pid）值（9,8）;
插入h（id，pid）值（10,9）;
插入h（id，pid）值（11,10）;
插入h（id，pid）值（12,9）;
插入h（id，pid）值（13,12）;
插入h（id，pid）值（14,8）;
插入h（id，pid）值（15,6）;
插入h（id，pid）值（16,15）;
插入h（id，pid）值（17，6）;
插入h（id，pid）值（18,17）;
插入h（id，pid）值（19,17）;
插入h（id，pid）值（20,3）;
插入h（id，pid）值（21,20）;
插入h（id，pid）值（22,21）;
插入h（id，pid）值（23,22）;
插入h（id，pid）值（24,21）;
结果
垂直同胞
|
| ____ 1
|
| ____ 2
|
| ____ 3
|
| ____ 4
| |
| | ____ 5
|
| ____ 6
| |
| | ____ 7
| | |
| | | ____ 8
| | |
| | | ____ 9
| | | |
| | | | ____ 10
| | | | |
| | | | | ____ 11
| | | |
| | | | ____ 12
| | | |
| | | | ____ 13
| | |
| | | ____ 14
| |
| | ____ 15
| | |
| | | ____ 16
| |
| | ____ 17
| |
| | ____ 18
| |
| | ____ 19
|
| ____ 20
|
| ____ 21
|
| ____ 22
| |
| | ____ 23
|
| ____ 24
水平同胞
|
|
|
0
|
|
|
|
|
1
|
|
|
|
|
2
|
|
|
|
|
3
|
|
---------------------------------------
| | |
| | |
4 6 20
| | |
| | |
| ------------------- |
| | | | |
| | | | |
5 7 15 17 21
| | | |
| | | |
| | ------ ------
| | | | | |
| | | | | |
8 16 18 19 22 24
| |
| |
-------- |
| | |
| | |
9 14 23
|
|
------
| |
| |
10 12
| |
| |
| |
| |
| |
11 13
解决方案
SQLite
垂直同胞
with last_sibling（id）
as
（
从h
中选择max（id）
by pid
）
，tree（id，branch，path）
as
（
select 1 as id
，'as branch b $ b，'001'as path
union all
选择h.id
，t.branch ||当ls.id不为空时，则'else'|'end ||''
，t.path ||'_'|| substr（'00000'|| h.id，-5）
从树t
离开加入last_sibling ls
对ls.id =
t.id
加入h
对h.pid =
t.id
）
，vertical_space（n）
as
（
select 1
union all
从vertical_space选择vsn + 1
vs
其中vsn < 2
）
选择t.branch ||如果是1，那么'| ____'||的情况下vs.n ''|| cast（t.id as text）else'|'从树中结束
t
交叉连接vertical_space vs
由t.path命令
，vs n desc
;
The initial motivation for this one, was to display Oracles' actual execution plans, saved in GV$SQL_PLAN, in a visual, clear way.
- I've attached my suggested solutions.
- Please feel free to add yours, as long as it fulfills the requirements.
- Please mention the database name your solution was written for.
Requirements
Input
- A table containing columns "id" (node's id) and "pid" (node's parent id).
Output
- The result should be a an ASCII art graph (see example below)
- Each pair of "id" and "pid" nodes should be connected with an edge.
- The root node might have an additional single edge.
- There should be no other edged especially not edges that are not connected to any node in one of their sides.
Code
- A single SELECT statement based only on native SQL
- No UDF (User Defined Functions).
- No T-SQL, PL/SQL etc.
Sample data
create table h (id int,pid int);
insert into h (id,pid) values (0 ,null);
insert into h (id,pid) values (1 ,0 );
insert into h (id,pid) values (2 ,1 );
insert into h (id,pid) values (3 ,2 );
insert into h (id,pid) values (4 ,3 );
insert into h (id,pid) values (5 ,4 );
insert into h (id,pid) values (6 ,3 );
insert into h (id,pid) values (7 ,6 );
insert into h (id,pid) values (8 ,7 );
insert into h (id,pid) values (9 ,8 );
insert into h (id,pid) values (10 ,9 );
insert into h (id,pid) values (11 ,10 );
insert into h (id,pid) values (12 ,9 );
insert into h (id,pid) values (13 ,12 );
insert into h (id,pid) values (14 ,8 );
insert into h (id,pid) values (15 ,6 );
insert into h (id,pid) values (16 ,15 );
insert into h (id,pid) values (17 ,6 );
insert into h (id,pid) values (18 ,17 );
insert into h (id,pid) values (19 ,17 );
insert into h (id,pid) values (20 ,3 );
insert into h (id,pid) values (21 ,20 );
insert into h (id,pid) values (22 ,21 );
insert into h (id,pid) values (23 ,22 );
insert into h (id,pid) values (24 ,21 );
Results
Vertical siblings
|
|____ 1
|
|____ 2
|
|____ 3
|
|____ 4
| |
| |____ 5
|
|____ 6
| |
| |____ 7
| | |
| | |____ 8
| | |
| | |____ 9
| | | |
| | | |____ 10
| | | | |
| | | | |____ 11
| | | |
| | | |____ 12
| | | |
| | | |____ 13
| | |
| | |____ 14
| |
| |____ 15
| | |
| | |____ 16
| |
| |____ 17
| |
| |____ 18
| |
| |____ 19
|
|____ 20
|
|____ 21
|
|____ 22
| |
| |____ 23
|
|____ 24
Horizontal siblings
|
|
|
0
|
|
|
|
|
1
|
|
|
|
|
2
|
|
|
|
|
3
|
|
---------------------------------------
| | |
| | |
4 6 20
| | |
| | |
| ------------------- |
| | | | |
| | | | |
5 7 15 17 21
| | | |
| | | |
| | ------ ------
| | | | | |
| | | | | |
8 16 18 19 22 24
| |
| |
-------- |
| | |
| | |
9 14 23
|
|
------
| |
| |
10 12
| |
| |
| |
| |
| |
11 13
解决方案
SQLite
Vertical siblings
with last_sibling (id)
as
(
select max (id)
from h
group by pid
)
,tree (id,branch,path)
as
(
select 1 as id
,'' as branch
,'001' as path
union all
select h.id
,t.branch || case when ls.id is not null then ' ' else '|' end || ' '
,t.path || '_' || substr ('00000' || h.id,-5)
from tree t
left join last_sibling ls
on ls.id =
t.id
join h
on h.pid =
t.id
)
,vertical_space (n)
as
(
select 1
union all
select vs.n + 1
from vertical_space vs
where vs.n < 2
)
select t.branch || case vs.n when 1 then '|____' || ' ' || cast (t.id as text) else '|' end
from tree t
cross join vertical_space vs
order by t.path
,vs.n desc
;
这篇关于SQL挑战/拼图：如何使用SQL查询创建ASCII艺术层次结构树？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
