MySQL选择与while循环 [英] mysql select with while loop

问题描述

我想从表中选择数据，并使用其中一列创建一个循环以定义其他数据.

I would like to select data from my table and using one of the columns, create a loop to define additional data.

例如:

'select id,related_id,name from ancestors'

id, related_id, name
1, 0, Bob
2, 1, Dave
3, 2, Susie
4, 1, Luke
5, 0, Cindy
6, 5, Sam

鲍勃是祖父，戴夫和卢克是他的孩子，苏西是他的孙女.辛迪生了一个孩子山姆.

现在，我想使用related_id来计算祖先树下降了多少级.所以我希望结果是:

Now, I want to use related_id to figure out how many levels the ancestor tree goes down. So I want the results to be:

id, related_id, name, level
1, 0, Bob, 0
2, 1, Dave, 1
3, 2, Susie, 2
4, 1, Luke, 1
5, 0, Cindy, 0
6, 5, Sam, 1

我想创建一个查询，例如:

I would like to create a query like:

select id,related_id,name from ancestors; 
while related_id<>0 do level=level+1; 
select related_id from ancestors where id=related_id end;

倒下树，计算个人在树内的等级.

Loop down the tree and count which level the individual is on inside his/her tree.

这是我的实现.我似乎无法使用get_level.我收到一个错误，无法在fetchall上使用布尔值.有什么问题?

Here's my implementation. I can't seem to use get_level. I get an error that I can't use boolean on fetchall. What's the problem?

   $connection->exec('
   DELIMITER $$
   DROP FUNCTION IF EXISTS `get_level` $$
   CREATE FUNCTION `get_level`(VAR int(11)) RETURNS int(11) 
   DETERMINISTIC
   BEGIN 
   DECLARE level int(11);
   DECLARE parent int(11);
   set level=0;
   set parent=(select related_id from category where id=VAR);
   while parent>0 DO
   set level=level+1;
   set parent=(select related_id from category where id=parent);
   END 
   WHILE; 
   return level;
   END$$
   DELIMITER;');

   $fetch=$connection->query('select *,get_level(id) as level from category')->fetchall(pdo::FETCH_UNIQUE|pdo::FETCH_ASSOC);
   print_r($fetch);

推荐答案

您可以创建mysql函数来获取记录级别，然后在查询中调用它. 输入的功能将是记录ID，并输出级别编号. 功能将是这样

you can create mysql function to get record level then call it in your query. the function input will be the record id and output the level number. function will be like this

DELIMITER $$
DROP FUNCTION IF EXISTS `getlevel` $$
CREATE FUNCTION `get_level`(Id int(11)) RETURNS int(11) 
    DETERMINISTIC
BEGIN 
DECLARE levelNumber int(11); -- declare variable to record level
DECLARE parent int(11); -- declare variable to hold the parent id
set levelNumber = 0; -- set the level to zero at the begining
set parent = (select `relation_column` from `table` where `id_column` = Id); -- get parent record of then id givin to function
while parent > 0 DO  -- loop unitl parent = 0 or record has no parent
set levelNumber = levelNumber + 1; -- increase level by 1
set parent = (select `relation_column` from `table` where `id_column` = parent); -- re set parent id
END 
WHILE; 
return levelNumber; -- return then level number
END$$
DELIMITER ;

relationship_column是保存记录关系的列. id_column是保存记录id或(主键)的列.

the relation_column is the column that hold record relation. the id_column is the column that hold the record id or (primary key).

最终查询将是这样

select `table`.`id_column`,`table`.`relation_column`,`table`.`name`,get_level(`table`.`id_column`) as  "level" from `table` 

希望这会有所帮助

这篇关于MySQL选择与while循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！