|
|
我将MySql与PHP结合使用，并将所有数据存储在这样的表中：
+ x& u. T5 T/ c: b$ Q"id" "name" "description" "level" "parent" "country" "maxLevel"
& [& ~% Z" l; ~' }8 I"1" "Kitchenware" "Kitchenware description" "1" "0" "US" "0", k6 e0 Z; B% L6 j7 s
"2" "Knives" "All our knives" "2" "1" "US" "0"
' ]0 @' m% T% H; M; p; C"3" "Butter Knives" "All Butter Knives" "3" "2" "US" "0"5 e4 L+ ^) \4 _1 T( r- c3 ^/ P
"4" "Cut em all" "Cut em all" "4" "3" "US" "0": [1 C$ [- z$ A! q
"5" "Cull em all" "Cull em all" "4" "3" "US" "0"
; H% w' b; h0 G/ H7 t"6" "Smear em all" "Smear em all" "4" "3" "US" "0"3 J! j, n7 E- K. ~& u, D
"7" "Meat Knives" "All Meat Knives" "3" "2" "US" "0"
& n4 z ^! I7 d7 e7 R"8" "Cut em meat" "Cut em meat" "4" "7" "US" "0"
) v$ `! Z) N' J* A- A"9" "Cull em meat" "Cull em meat" "4" "7" "US" "0"& D2 M4 a# |, e
"10" "Smear em meat" "Smear em meat" "4" "7" "US" "0"
( m' T- o' _9 n; b$ B1 D由此，如果我有例如：id = 10，sql将如何显示项目的层次结构？: j! h/ u3 k' l% t
因此，对于id = 10，层次结构为：. z7 ^. }* q; f% Q
Kitchenware > Knives > Meat Knives > Smear em meat
. H& b: C/ C; i* F5 G$ G: i对于id = 7，层次结构为：
5 J7 W0 ]/ L3 J# j7 ZKitchenware > Knives > Meat Knives
# \( [ \% m% }' k% a对于id = 4，层次结构为+ T- Y; n' ]- N8 [4 R
Kitchenware > Knives > Butter Knives > Cut em all
- {$ ] h8 v$ N0 f" `" m等等。任何想法如何构造SQL来实现这一目标？/ g- z' G+ B" z. ^7 R7 f% `4 A
2 k4 @. m1 t( j
解决方案:; c: c0 v+ G# x% I0 r) X8 ]
8 G+ d: E2 R3 q2 Z( z3 d3 [& @+ { `. h" A
7 w5 J" ~0 n! X" Q4 I5 l 试试这个存储过程
7 \5 ]: A& h/ xCREATE PROCEDURE updatePath(in itemId int); u0 _9 u1 J9 ?* c' Q- V
BEGIN
! x% b$ d& J( X; u DECLARE cnt int default 0;
* a* E$ F. t \: f CREATE temporary table tmpTable
/ A& M0 S0 m) F8 D' M (8 O4 ^0 ~% ]- p- p; R
`id` int, `name` varchar(15), `parent` int, path varchar(500) . [2 _4 q! |4 ]& g7 b0 d
)engine=memory select id, name, parent, name AS 'Path' from tbl where id = itemId;
, x' T. h# G* n7 @- U8 c select parent into cnt from tmpTable;
6 g, f5 x/ J& \' k( J while cnt 0 do
3 c: H7 J( K+ q, F' V3 ]2 Q Update tmpTable tt, tbl t set tt.parent = t.parent,
6 Y: W$ [# I$ p1 Y% I/ u tt.path = concat(t.name, ' > ', tt.path)' m! ~. G% D e5 e
WHERE tt.parent = t.id;
& P5 P" Z) h% L! |0 k- N* Q select parent into cnt from tmpTable;
% T, r9 X$ s1 Q* M# \ end while;& n5 y, x( ?/ P/ e
select * from tmpTable;
. \0 {5 \' n* O5 G drop table tmpTable;0 @ X0 ^" h. b; S2 Q* D; H" ]
END//* ^( b/ h* w3 a w! b+ s
查询1 ：
2 s/ c+ [) {" `8 d5 Fcall updatePath(10)) G7 d: A/ P! w7 D
SQL FIDDLE ：
# I0 N9 d; N6 C7 Q4 L4 q| ID | NAME | PARENT | PATH |
6 N# C( V* v. t1 ^, {----------------------------------------------------------------------------------------------
6 i* o8 m% J' f: [3 Q- V| 10 | "Smear em meat" | 0 | "Kitchenware" > "Knives" > "Meat Knives" > "Smear em meat" |' D- ~- T0 }, V5 _6 i0 [; h* x
希望这可以帮助 |
|
技术问答
314 人阅读
|
0 人回复
|
2023-09-14
