在 MySQL 中生成分层数据集 [英] Produce hierarchical dataset in MySQL

问题描述

我有一个用户 ID 值表，A 列包含管理用户，B 列包含用户.例如，我希望能够显示管理其他用户的用户列表.

<前>用户 1 可以管理用户 2用户 1 可以管理用户 3用户 3 可以管理用户 4用户 5 可以管理用户 6

这会产生下表:

<前>--------------------------|管理用户 ID |用户 ID |--------------------------|1 |2 ||1 |3 ||3 |4 ||5 |6 |--------------------------

我想要一个查询或存储过程，通过在层次结构中跟踪他们来返回管理用户的列表.因此，如果您是用户 1，则会输出以下列表.

 1, 2, 3, 4 

那么如果您是用户 2，那么您将只有 2 个输出.

 2 

如果您是用户 5，则输出以下列表，依此类推.

 5, 6 

鉴于我拥有每个用户 ID，实现这一目标的最佳方法是什么.

提前致谢.

解决方案

替换你的表名上的 YOUR_TABLE

DELIMITER $$CREATE PROCEDURE get_users(IN base INT UNSIGNED)开始DECLARE ids TEXT DEFAULT '';SET @parents = 基数；SET ids = 基数；循环 1:循环SET @stm = CONCAT('SELECT GROUP_CONCAT(userId) INTO @parents FROM YOUR_TABLE',' WHERE mgtuserId IN (', @parents, ')');从@stm 准备 fetch_childs;执行 fetch_childs;删除准备 fetch_childs;如果@parents 为空，则离开loop1；万一;SET ids = CONCAT(ids, ',', @parents);结束循环；SET @stm = CONCAT('(SELECT mgtuserId FROM YOUR_TABLE WHERE mgtuserId=',base,') UNION (SELECT userId FROM YOUR_TABLE WHERE userId IN (',ids, '))');从@stm 准备 fetch_childs;执行 fetch_childs;删除准备 fetch_childs;结尾;

检查

CALL get_users(1);1234

并在 my.ini 中设置

thread_stack = 256K

I have a table of ID values of users column A has the management users and column B has the users. I want to be able to display a list of user that manage other users, for example.

User 1 can manage user 2
User 1 can manage user 3
User 3 can manage user 4
User 5 can manage user 6

This produces the following table:

--------------------------
|  mgtuserId  |  userId  |
--------------------------
|     1       |     2    |
|     1       |     3    |
|     3       |     4    |
|     5       |     6    |
--------------------------

I would like to have a query or stored procedure that returns the list of management users by following them down the hierarchical structure. So that if you are user 1 the following list will be output.

 1, 2, 3, 4 

Then if you are user 2 then you will only have 2 output.

 2 

Then if you are user 5 the following list is output and so on.

 5, 6 

What is the best way to achieve this given that I have each users Id.

Thanks in advance.

解决方案

replace YOUR_TABLE on your table name

DELIMITER $$

CREATE PROCEDURE get_users(IN base INT UNSIGNED)
BEGIN
DECLARE ids TEXT DEFAULT '';

SET @parents = base;
SET ids = base;

loop1: LOOP
    SET @stm = CONCAT(
        'SELECT GROUP_CONCAT(userId) INTO @parents FROM YOUR_TABLE',
        ' WHERE mgtuserId IN (', @parents, ')'
    );

    PREPARE fetch_childs FROM @stm;
    EXECUTE fetch_childs;
    DROP PREPARE fetch_childs;

    IF @parents IS NULL THEN LEAVE loop1; END IF;

    SET ids = CONCAT(ids, ',', @parents);
END LOOP;

SET @stm = CONCAT('(SELECT mgtuserId FROM YOUR_TABLE WHERE mgtuserId=',base,') UNION (SELECT userId FROM YOUR_TABLE WHERE userId IN (',ids, '))');

PREPARE fetch_childs FROM @stm;
EXECUTE fetch_childs;
DROP PREPARE fetch_childs;
END;

checking

CALL get_users(1);
1
2
3
4

and set in my.ini

thread_stack = 256K

这篇关于在 MySQL 中生成分层数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！