|
这样做的最初动机是以直观,清晰的方式显示保存在GV $ SQL_PLAN中的Oracle的实际执行计划。
% n. p( H1 e6 S7 K% \3 q& r7 ~( a. V/ B$ M6 S1 P
我已附上我建议的解决方案。
5 b. ~! D$ N* `' R3 e2 ~只要满足要求,请随时添加您的内容。
, w, `# j- Y& Y5 I$ x请提及您的解决方案的数据库名称。
' o# x8 k. W7 J8 B0 h& i
* o! _% x* T1 O* {0 j8 D A* z/ P. W
要求6 A9 Q& W# W* f
输入* R% I. l7 J! P2 m$ B
一个包含“ id”(节点的ID)和“ pid”(节点的父ID)列的表。 " K; i# x5 b) R i
1 g" W7 d. u3 Z7 |7 x2 j
输出
& g% Y5 Y s% ~ `结果应为ASCII艺术图(请参见下面的示例)
. Q4 i8 ^ U* M4 |每对“ id”和“ pid”节点都应连接一条边。3 k% R- `5 ~1 R, M" `- y& ?4 o; _- j
根节点可能有一个附加的单边。5 f! p* Y5 J M- e
不应有其他未边缘化的边缘,尤其是没有在其一侧中未连接到任何节点的边缘。 8 I7 f5 l6 C7 l- H. P( W
; d6 t/ p. o: E3 A" L
2 @5 _6 j, \2 |4 E7 Q0 a4 N% b代码
* n) _. n3 }4 F- z/ o仅基于本机SQL的单个SELECT语句 % Z' @4 E: n. B$ U0 \* D; U( Q
没有UDF(用户定义函数)。
* J. }( u- s) n, ~没有T-SQL,PL / SQL等
" F( v: B( v2 t% m+ ^) u* b2 M! y( H8 ]/ f: p1 j2 |; o
- N( }' J; g" P& X7 O3 z
样本数据' |6 O& S* p7 I ~- ~
create table h (id int,pid int);9 c: I0 U" w' O% g5 r6 J7 ^& D
insert into h (id,pid) values (0 ,null);
4 D# `) U' I5 I$ M9 Qinsert into h (id,pid) values (1 ,0 );4 b9 S2 i& s- G
insert into h (id,pid) values (2 ,1 );
% Q" r6 `. Q: O$ p! Ainsert into h (id,pid) values (3 ,2 );
1 o f7 A8 k% ~- }2 M uinsert into h (id,pid) values (4 ,3 );4 D* D" R- H7 ?. h! i* x
insert into h (id,pid) values (5 ,4 );
: G k+ A( ^- L$ |6 Minsert into h (id,pid) values (6 ,3 );
& @6 [" u2 W$ m. x5 Binsert into h (id,pid) values (7 ,6 );/ z4 C; R+ t L7 M2 F4 V
insert into h (id,pid) values (8 ,7 );
. h& [7 V( D$ f# x; Q/ Minsert into h (id,pid) values (9 ,8 );
\7 g" f5 m9 q G+ jinsert into h (id,pid) values (10 ,9 );* M/ @+ }/ r) g. y6 U$ G* g/ N. U) X% K1 n
insert into h (id,pid) values (11 ,10 );
. o- J! `! D2 _( K5 i! `9 ?insert into h (id,pid) values (12 ,9 );; S2 D& { w* |
insert into h (id,pid) values (13 ,12 );
: i# z, @$ d! [# Yinsert into h (id,pid) values (14 ,8 );
8 s- @! }" s3 C$ Minsert into h (id,pid) values (15 ,6 );4 r' w: s- B5 v s: t
insert into h (id,pid) values (16 ,15 );/ C4 X& \/ s( y4 U& A Z+ m% ?+ X
insert into h (id,pid) values (17 ,6 );
0 z5 K9 [% I6 F+ C. {insert into h (id,pid) values (18 ,17 );* Y/ _" y9 g0 w: g+ a" h0 T& e9 y
insert into h (id,pid) values (19 ,17 );
P( Y* {9 c# Y; }5 W7 Binsert into h (id,pid) values (20 ,3 );
- d5 ^" A; v9 |insert into h (id,pid) values (21 ,20 );
9 Y. M$ c7 Q' [, d. P7 L: pinsert into h (id,pid) values (22 ,21 );6 m8 j/ c1 Q8 r0 g
insert into h (id,pid) values (23 ,22 );
1 d X [0 K: z" v# _insert into h (id,pid) values (24 ,21 );
2 o J) l; S" j! l w; o结果
7 ^% J) S, h* ~- I eVertical siblings
( J3 l/ F8 P% k9 ^: v|
8 x+ X c- ~3 A" z|____ 1
7 p; O0 J* _: B/ u" m |
) O. ~! i& V- X) T3 O) q |____ 21 V) P0 Q! L# m! N* ^; r
|
. b7 ~# K4 U7 x- K+ i+ G |____ 3" j2 T7 T0 R8 r' D; s3 |5 p9 \
|
. N+ T! n. @% X% ]3 ~' B* K; A% b: s |____ 4" o" G# f) E. k, R$ p
| |
6 w/ ]( F& c E5 V" r | |____ 5
4 J, f1 }) u8 l2 J1 \) ^8 F |9 L4 g+ I0 v/ Y+ {: p; }2 U) U5 Q
|____ 66 w6 |& `$ q/ ]
| |
0 W: P0 P- t+ }& n2 U2 ~( @# U7 R7 v | |____ 70 _" r/ [1 y: w7 o+ J R6 x: Y% s) q
| | |
. f0 |0 A& w% O% O6 F3 o0 u | | |____ 8
1 W( ^/ g+ q- f: q* F2 j* [; x* B | | |1 L/ ~2 S- a' e
| | |____ 9
* f; C& M) V) d; Q6 m% R | | | |( H& Z6 @. D% w& I
| | | |____ 10
, B1 x/ c# C: R | | | | |
0 H' G1 a5 t8 M3 a" A9 V | | | | |____ 115 ~ M. e! \1 O( W
| | | |, f: x% L4 G) f% R( n3 o0 K
| | | |____ 12& s! U9 h0 u; {" j1 r/ C0 |
| | | |. _! f* D5 P4 P+ {% [
| | | |____ 13
& P! Z8 U9 X+ t+ ~ | | |) s S1 w) B7 h& ~, D/ O$ S( Z
| | |____ 14
- t* [! w3 {+ V% `; k | |
7 i; E1 y- [ M9 {3 g& ?* l* } | |____ 15
8 O( Z3 Z+ E5 s9 O | | |9 @0 y% A @5 `* w$ x2 T
| | |____ 167 V$ G C, z* X
| |+ I; {5 s( G. i, m& L
| |____ 172 @5 i$ D- C/ C# R9 ^7 j9 p, E
| |1 g: }* { E& Y6 x- T) X
| |____ 18
" g8 L$ ^8 G4 W3 b$ E | |
" v |! Y# f6 i: h | |____ 19
( B+ n1 G% I5 u3 ~ |
" _; {5 |* d' N* I |____ 20
, e; ?; V8 c2 ^5 l |* H# B; X, S) T2 ~6 v, J0 e
|____ 21
1 z1 w5 X* E$ Y" f1 ` |
7 ~) r k% z# _, @ |____ 225 M, S5 h" z& D
| |( C2 {+ _9 k- r) s
| |____ 23+ |- T1 F$ c: B
|
7 I# {2 t6 s. B8 U! J |____ 24
' u: R, q: t2 {: RHorizontal siblings
3 D3 N3 g9 y+ P1 W5 K |
- V B% u3 e, ?* r | : f" T/ q( l2 } w4 y, {
| ; }4 a. D! \& L) N/ X: ]+ s& ]8 j) N
0
. Y9 o M: N. u% i9 h+ } | " j5 ]! T0 W* p- r$ \" ]& U
|
2 i0 t) m- c: D! ]% q |
' ] a4 V2 F8 ?- M |
; ]* i8 P* i1 N2 [3 B& ] l8 |: x |
/ W% o: Q0 g3 @6 E$ p 1
2 w! T" t4 q; i4 H | 1 H! Y2 _1 m' j3 r. L1 f' _
| - `4 I& L, _5 }% s
|
8 ?3 u" {- I4 d7 o( k9 T7 o8 s. q |
0 S$ V$ B' P4 W9 T' t | 6 ^) \3 Z8 s ]- U
2
. y6 a; R$ E8 I( C% ~ |
5 ^4 m. w# f9 g8 P) [8 N | ( D% ]# \ j/ r* g" k
| - ^" |% S! k) k4 L
|
3 x2 }7 n5 p% B4 z | : V" ` I" ^8 {) f% Z/ M
3 1 z6 U1 H: G, C0 v# V* r
| " E" j0 X) ]+ ?; U8 J/ P# g
|
4 u0 O* x9 d# x2 y --------------------------------------- ( }* C* u; j, i; y. L: F
| | | ( d2 b. w1 l$ O& y- B6 P l
| | |
A: \& t, }: V' }/ I6 X# W 4 6 20
* `0 C1 \% v- ?2 g | | | * @* K# Z3 O7 W
| | |
) J$ r; [ Z F3 H' l7 y( s | ------------------- | # H- V: T" H3 W% e8 t
| | | | |
2 y0 m# R- D2 t, d2 @, [6 | | | | | | 9 z# `) b1 w. |* Q6 k! Y
5 7 15 17 21 : ]! \$ k* \2 b6 z& \
| | | |
" ^& i/ A5 Q. V) I; Z. |2 m | | | |
5 }4 c+ r; T4 ?) m; p- P9 h | | ------ ------
: c: k/ u/ b4 p& l, o) g | | | | | |
. f+ k) C+ h0 Y | | | | | |
, V2 G. ^' u, T t, C/ A% r 8 16 18 19 22 24 ! ]0 W( M6 n* g: j
| |
8 G: q% q1 G }7 j! L* @ | | 1 R; h0 ~; K+ O6 }8 `3 J5 t
-------- | , s6 r7 ]$ E _& g; Y c" x
| | |
6 B9 M1 t( B9 F1 P- R+ G | | |
" E U3 q s8 |& I5 u 9 14 23 * r. Q Z- c1 I8 Y, I" e
|
3 @. F3 E) w. s |
& b6 w2 O7 Q5 N( ?& I ------
+ C9 {: o. t& B | |
" |4 A5 q% Q% k: {8 Z | |
$ i8 i, g4 m6 T) u+ \( ]1 c 10 12 8 [7 Z/ ^2 ]4 o0 K; d' e
| | 8 M% D& q3 c% w
| | 7 U. b% d1 M N2 ]4 |
| |
! h& R% |, |! o0 H4 _ | |
2 V3 r2 ^: P6 J$ r | |
3 w# W4 T8 M$ R8 a6 v 11 138 ?8 g% K/ k$ b" ?: c. A
1 ~3 ] U/ Q0 A$ w. i4 L解决方案:! g4 |( B" _! S9 a! }+ G8 Q, J# m
% v$ v/ A, w* C9 b8 L! R5 v; e4 \
6 {8 l' v, S( c& c7 [+ f4 Z4 F
$ E2 j5 Q R* C9 o6 P
SQLite9 t& H1 }: @1 N9 j5 B
Vertical siblings/ ?+ P9 J7 r: G n5 b1 y) c8 h1 @$ c
with last_sibling (id)
& L! t4 {, |+ B) L4 j as
- w$ C h2 @# H- q' p () Y5 m+ p: ]/ d% _+ F) x3 D
select max (id)9 h0 @$ w. I4 e5 O' A2 I: a% m
from h- @1 t( ^; Y4 V2 _
group by pid) O6 M, N9 l+ \- s8 n! L/ _# p
)
3 o: { o" ~' m. K- q- s ,tree (id,branch,path)
" @; z0 e% w, @ T5 m' j6 J as) D) J6 a5 Q% p- A/ r, R' H
(9 M7 b5 c! w/ M; d3 }
select 1 as id8 i M; J" W; c$ V$ P8 u/ ~
,'' as branch
$ _7 J2 Y) f; v; a; a ,'001' as path& {# b3 q! o0 d+ l; u6 S
union all
# u0 ~+ d2 ^* o select h.id
$ S# A/ L; }6 [- Q4 \4 P( ] ,t.branch || case when ls.id is not null then ' ' else '|' end || ' '0 W6 a" ^: v6 Y+ K$ l
,t.path || '_' || substr ('00000' || h.id,-5)
9 x+ n; t6 w) p' G; G7 m from tree t
& t0 ?7 O% D% F left join last_sibling ls
- V1 E. r# E: P: s7 X3 ~ on ls.id =, n4 N* ^& L. \8 ?* q
t.id
5 e5 y; O7 O/ J" ?$ d5 l join h
$ d) _, H0 S- A. B& m+ a on h.pid =/ v' k: ?' h: h0 j* Q" S6 b" J
t.id
4 B/ E. U0 z0 x9 S" c, G ), i( l% x9 E( [
,vertical_space (n)5 O3 G/ o3 q) Z+ B9 H/ m) ?
as
$ ~/ l8 X' p- @: I; P (
* x) G% Y- K5 v i! { select 16 D5 M9 w; D$ V3 x' u
union all
5 E( k3 B# y4 Z- \ select vs.n + 1/ [' V K: `* b, Q$ O
from vertical_space vs
4 O9 q6 B/ E- t* s where vs.n < 2
y8 m O% F; e6 |) \ J )8 s( ?' |; V" O
select t.branch || case vs.n when 1 then '|____' || ' ' || cast (t.id as text) else '|' end
7 ]7 S: T4 C, E6 ^- K1 ^8 ~2 O; A" efrom tree t
: y7 p; c3 S; ~4 a5 i* ?6 P. V cross join vertical_space vs7 D1 \7 g* u! y
order by t.path
" I8 z$ O. {# G, e4 T; L- r ,vs.n desc
6 Y1 G, U1 B4 U" q/ f; |
|