本文介绍了mysql表中的父树(while循环)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
对不起,我的英语. 我有一个像这样的mysql表
Sorry for my English. I have a mysql table like this
[ --------------------------] [ parent_id ] [ category_id ] [ --------------------------]
网站的结构如下:
Site structured like this:
0 -> 1 -> 2 -> -> 3 -> -> -> 5 -> 4和表看起来像
0 1 0 2 2 3 0 4 3 5如何在while循环中编写mysql以输入5并获取其父级列表直到0:
How to write mysql while loop to input 5 and get list of it's parents until 0:
3 2我知道如何用php编写它,但是我只想对数据库进行1次查询,但是当我尝试运行官方手册中的"While"示例时,它会返回很多错误.
I know, how to write it in php, but I want to do only 1 query to database, but when I try to run "While" examples from official manuals, it returns a lot of errors.
推荐答案每个答案都不正确,但是我已经做到了. 如果有人需要,请尝试此.
Every answers are incorrect, but I've done it. If somebody will need, try this.
DELIMITER $$ DROP PROCEDURE IF EXISTS `dbName`.`getParentsTree` $$ CREATE PROCEDURE `tableName`.`getParentsTree` (IN firstChild INT, OUT tree VARCHAR(255)) BEGIN set @newChar = (select `parent_id` from tableName where category_id = firstChild); set @fullchar = ""; set @fullchar = @fullchar + firstChild; WHILE (@newChar != 0) DO SELECT CONCAT_WS(',', @fullChar, @newChar) INTO @fullChar; set @newChar = (select `parent_id` from tableName where category_id = @newChar); END WHILE; SELECT @fullchar INTO tree; END $$ DELIMITER ; CALL dbName.getParentsTree(46, @a); SELECT @a;更多推荐
mysql表中的父树(while循环)
发布评论