mysql - Finding the leaf category in a table using recursive procedure in mysql

Question

Need to have all the leaf categories for a particular category.

For eg: i am having a table and data in that table as below

CREATE TABLE `world`.`category_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `category_id` BIGINT(20) NULL,
  `sub_category_id` BIGINT(20) NULL,
  PRIMARY KEY (`id`));


insert into world.category_table (category_id, sub_category_id) values (3267,4202);
insert into world.category_table (category_id, sub_category_id) values (3267,4205);
insert into world.category_table (category_id, sub_category_id) values (3267,4208);
insert into world.category_table (category_id, sub_category_id) values (4202,4203);
insert into world.category_table (category_id, sub_category_id) values (4202,4204);
insert into world.category_table (category_id, sub_category_id) values (4205,4206);
insert into world.category_table (category_id, sub_category_id) values (4205,4207);
insert into world.category_table (category_id, sub_category_id) values (4208,4209);
insert into world.category_table (category_id, sub_category_id) values (4208,4210);
insert into world.category_table (category_id, sub_category_id) values (4208,4453);
insert into world.category_table (category_id, sub_category_id) values (4208,4454);
insert into world.category_table (category_id, sub_category_id) values (4208,4457);
insert into world.category_table (category_id, sub_category_id) values (4208,4458);
insert into world.category_table (category_id, sub_category_id) values (4209,4452);
insert into world.category_table (category_id, sub_category_id) values (4210,4455);
insert into world.category_table (category_id, sub_category_id) values (4210,4456);
insert into world.category_table (category_id, sub_category_id) values (4458,4459);
insert into world.category_table (category_id, sub_category_id) values (4458,4460);
insert into world.category_table (category_id, sub_category_id) values (4458,4461);
insert into world.category_table (category_id, sub_category_id) values (4458,4462);
insert into world.category_table (category_id, sub_category_id) values (4458,4463);
insert into world.category_table (category_id, sub_category_id) values (4458,4464);

Now i want to have a leaf node for a particular category_id

for eg if i pass 3267 as input then output should be : 4203,4204,4206,4207,4452,4455,4456,4453,4454,4457,4459,4460,4461,4462,4463,4464,

I have written a procedure for this which is mentioned below

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllLeafCategories`(IN CATEGORYID BIGINT(20), INOUT RESULTSTRING longtext)
BEGIN
    DECLARE done int default 0;
    DECLARE SUBCATEGORYID BIGINT(20);
    DECLARE CATEGORY_CURSOR CURSOR FOR SELECT SUB_CATEGORY_ID FROM world.category_table where category_id = CATEGORYID;
    DECLARE CONTINUE handler for not found SET done = 1;
    OPEN CATEGORY_CURSOR;
    SUBCATEGORYLOOP : LOOP
        FETCH CATEGORY_CURSOR INTO SUBCATEGORYID;
        IF done THEN
            if RESULTSTRING is null then 
                set RESULTSTRING = '';
            end if;
            SET RESULTSTRING = concat(RESULTSTRING, CATEGORYID, ',');
            LEAVE SUBCATEGORYLOOP;
        ELSE 
            SET CATEGORYID = SUBCATEGORYID;
            CALL GetAllLeafCategories(CATEGORYID, RESULTSTRING);
        END IF;
    END loop SUBCATEGORYLOOP;
     CLOSE CATEGORY_CURSOR;
END;

executed procedure with following input

set @RESULTSTRING = '';
call world.GetAllLeafCategories(3267, @RESULTSTRING);
select @RESULTSTRING;

i got output as follows : 4203,4204,4204,4206,4207,4207,4452,4452,4455,4456,4456,4453,4454,4457,4459,4460,4461,4462,4463,4464,4464,4458,4208,

which is not correct output

can any one correct me or if possible can u provide a recursive procedure for this.

标签： mysql

Answer 1

Assuming that you are not using mysql 8.0, here is a solution for your need with just one query :

MY SQL 5.7

select 
  sub_category_id as leaf_id
from
  (
    select 
      * 
    from category_table
    order by category_id, sub_category_id
  ) products_sorted,
  (
    select @pv := '3267'
  ) initialisation
where find_in_set(category_id, @pv)
and length(@pv := concat(@pv, ',', sub_category_id))
and sub_category_id not in (select  distinct category_id from category_table)

FIND A DEMO HERE

IMPORTANT : Note that 5.7 solution will only work if category_id is always lower than sub_category_id, which seems to be your case.

If you are using mysql 8.0, you could use recursive cte as follow :

MY SQL 8.0

with recursive cte (sub_category_id, category_id) as 
(
  select
    sub_category_id,
    category_id
  from category_table
  where category_id = 3267
  union all
  select
    ct.sub_category_id,
    ct.category_id
  from category_table ct
  inner join cte on ct.category_id = cte.sub_category_id
)
select sub_category_id as leaf_id 
from cte 
where sub_category_id not in (select distinct category_id from category_table)

FIND A SECOND DEMO HERE