MySQL创建一个循环自身的存储过程

Question

我想创建一个mysql函数，它可以使用表related中的列category查找所有相关的祖先，然后使用所有祖先(子代、孙辈.)ids包括其自身，以使用列listing_category查找这些ids的所有实例。

范畴

ID, Related
1,0
2,1
3,1
4,1
5,0
6,5
7,1
8,7
9,7
10,1

如果我选择1，那么2,3,4,7,10是它的孩子，8,9是它的孙子。

listing_category

Category
1
1
2
3
3
5
6
9
7
7

现在我要创建一个MySql函数，它可以计算另一个名为listing_category的表中1,2,3,4,7,10,8,9的所有实例

   create function listing_count(ID int(11)) returns int(11)
   begin 
    declare count int(11);
    set count=(select count(*) from listing_category where category=ID);
    while (select id from category where related=ID) as childID and count<100 do
     set count=count+listing_count(childID);
    end while;
    return count; 
   end

因此，listing_count(1)将在category中找到所有亲属2,3,4,7,10,8,9，然后在listing_category中计数1,2,3,4,7,10,8,9的所有实例。因此，在本例中将返回8的计数。

可以用mysql存储过程吗？

Answer 1

您可以使用递归存储过程来完成此操作。它的优点是，它的工作不考虑祖先的深度，例如子女、孙辈、曾孙等。

delimiter //
drop PROCEDURE if EXISTS listing_count //
create procedure listing_count(in parentID int(11), out thesum int(11))
begin 
  declare childID int(11);
  declare childSum int(11);
  declare finished int default 0;
  declare childID_cursor cursor for select id from category where related=parentID;
  declare continue handler for not found set finished = 1;
  select count(*) into thesum from listing_category where category=parentID;
  open childID_cursor;
  get_children: LOOP
    fetch childID_cursor into childID;
    if finished = 1 then 
      LEAVE get_children;
    end if;
    call listing_count(childID, childSum);
    set thesum = thesum + childSum;
  end loop get_children;
  close childID_cursor;
end
//

对于您的数据，此查询将产生预期的结果(8)：

SET @@SESSION.max_sp_recursion_depth=25;
call listing_count(1, @x);
select @x;

如果您真的想要一个函数，您可以将该过程封装在一个函数中(因为MySQL不允许您创建递归函数)：

DELIMITER //
drop function if exists lc//
create function lc(id int(11)) RETURNS int(11)
BEGIN
  declare sum int(11);
  call listing_count(id, sum);
  return sum;
END
//
select lc(1)

输出：

8

Answer 2

如果希望在一列中包含所有相关类别，则需要合并来自l1 (仅一行)、l2和l3的所有If。

SELECT l1.* FROM category l1
WHERE l1.id = ID
UNION ALL
SELECT l2.* FROM category l1
    INNER JOIN category l2 ON l1.related = l2.id
WHERE l1.id = ID
UNION ALL
SELECT l3.* FROM category l1
    INNER JOIN category l2 ON l1.related = l2.id
    INNER JOIN category l3 ON l2.related = l3.id
WHERE l1.id = ID

一旦您拥有了所有的ID，您就可以得到计数：

下面是一个查询，它将为您计算记录：

SELECT COUNT(*) FROM listing_category 
WHERE category IN (
SELECT l1.* FROM category l1
    WHERE l1.id = ID
    UNION ALL
    SELECT l2.* FROM category l1
        INNER JOIN category l2 ON l1.related = l2.id
    WHERE l1.id = ID
    UNION ALL
    SELECT l3.* FROM category l1
        INNER JOIN category l2 ON l1.related = l2.id
        INNER JOIN category l3 ON l2.related = l3.id
    WHERE l1.id = ID)