如何查询MySql表以显示根及其子级. [英] How to query a MySql table to display the root and its subchild.

问题描述

UserID      UserName       ParentID      TopID
  1         abc            Null           Null
  2         edf             1             1
  3         gef             1             1
  4         huj             3             1
  5         jdi             4             1
  6         das             2             1
  7         new            Null           Null
  8         gka             7             7

TopID和ParentID来自用户ID

TopID and ParentID is from the userID

我想获取用户记录及其子记录和子记录.这里的userid1是根，其子级是userid2和userid3.因此，如果用户id是1，则我必须显示从userid 1到userid 6的所有记录，因为所有记录都是根的child和SUbchild.同样，对于userid3，我必须显示userid3及其子Userid 4和Userid 4的子代Userid5 如果用户ID是3

I Want to get a user record and its child and subchild record. Here userid1 is the root and its child are userid2 and userid 3. So If the user id is 1 I have to display all the records from userid 1 to userid 6 since all are child and SUbchild of the root. Similarly for userid3 I have to display userid3 and its child Userid 4 and Child of Userid 4 Userid5 if the userid is 3

输出应为

Userid  Username
3          gef
4          huj
5          jdi

我将知道用户ID和topID，因此如何进行查询才能获得上述结果.

I will know the userid and the topID so how can I do the query to acheive the above result.

SELECT UserID, UserName  FROM tbl_User WHERE ParentID=3 OR UserID=3 And TopID=1;

通过上面的查询，我能够显示用户ID 3和用户ID 4，但我不能显示用户ID 5，其中有种.需要帮忙.谢谢

By the above query I am able to display userid 3 and userid 4 I am not able to display userid 5, Kind of struck in it. Need help. Thanks

推荐答案

从技术上讲，可以使用存储过程在MySQL中进行递归层次查询.

It is technically possible to do recursive hierarchical queries in MySQL using stored procedures.

以下是适合您情况的一种:

Here is one adapted to your scenario:

CREATE TABLE `user` (
  `UserID` int(16) unsigned NOT NULL,
  `UserName` varchar(32),
  `ParentID` int(16) DEFAULT NULL,
  `TopID` int(16) DEFAULT NULL,
  PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user VALUES (1, 'abc', NULL, NULL), (2, 'edf', 1, 1), (3, 'gef', 1, 1), 
 (4, 'huj', 3, 1), (5, 'jdi', 4, 1), (6, 'das', 2, 1), (7, 'new', NULL, NULL), 
 (8, 'gka', 7, 7);

DELIMITER $$
DROP PROCEDURE IF EXISTS `Hierarchy` $$
CREATE PROCEDURE `Hierarchy` (IN GivenID INT, IN initial INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE next_id INT;

    -- CURSOR TO LOOP THROUGH RESULTS --
    DECLARE cur1 CURSOR FOR SELECT UserID FROM user WHERE ParentID = GivenID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- CREATE A TEMPORARY TABLE TO HOLD RESULTS --
    IF initial=1 THEN
        -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) --
        DROP TABLE IF EXISTS OUT_TEMP; 
        CREATE TEMPORARY TABLE OUT_TEMP (userID int, UserName varchar(32));
    END IF;

    -- ADD OURSELF TO THE TEMPORARY TABLE --
    INSERT INTO OUT_TEMP SELECT UserID, UserName FROM user WHERE UserID = GivenID;

    -- AND LOOP THROUGH THE CURSOR --
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO next_id;

        -- NO ROWS FOUND, LEAVE LOOP --
        IF done THEN
        LEAVE read_loop;
        END IF;

        -- NEXT ROUND --
        CALL Hierarchy(next_id, 0);     
    END LOOP;

    CLOSE cur1;

    -- THIS IS THE INITIAL CALL, LET'S GET THE RESULTS --
    IF initial=1 THEN
    SELECT * FROM OUT_TEMP;
        -- CLEAN UP AFTER OURSELVES --
        DROP TABLE OUT_TEMP; 
    END IF;
END $$
DELIMITER ;

CALL Hierarchy(3,1);
+--------+----------+
| userID | UserName |
+--------+----------+
|      3 | gef      |
|      4 | huj      |
|      5 | jdi      |
+--------+----------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

CALL Hierarchy(1,1);
+--------+----------+
| userID | UserName |
+--------+----------+
|      1 | abc      |
|      2 | edf      |
|      6 | das      |
|      3 | gef      |
|      4 | huj      |
|      5 | jdi      |
+--------+----------+
6 rows in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

是时候指出一些洞穴了:

• 由于这是递归调用存储过程，因此您需要增加

• Since this is recursively calling a stored procedure, you need to increase the size of max_sp_recursion_depth, which has a max value of 255 (defaults to 0).

我在具有有限测试数据(user表的10个元组)的非繁忙服务器上的结果完成了0.07-0.10秒.这种性能使得最好将递归放在您的应用程序层中.

My results on a non-busy server with the limited test data (10 tuples of the user table) took 0.07-0.10 seconds to complete. The performance is such that it might be best to put the recursion in your application layer.

我没有利用您的TopID列，因此可能存在逻辑缺陷.但是两个测试用例给了我预期的结果.

I didn't take advantage of your TopID column, so there might be a logic flaw. But the two test-cases gave me the expected results.

_{免责声明:此示例只是为了证明它可以在MySQL中完成，而不是我仍然赞同.存储过程，临时表和游标也许不是解决此问题的最佳方法.}

这篇关于如何查询MySql表以显示根及其子级.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！