How to query a MySql table to display the root and

问题:

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 and ParentID is from the userID

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

output should be

Userid  Username
3          gef
4          huj
5          jdi

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;

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

回答1:

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)

Time to point out some caveats:

• 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).

• 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.

• 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.

_{Disclaimer: This example was just to show that it can be done in MySQL, not that I endorse it in anyway. Stored Procedures, temporary tables and cursors are perhaps not the best way to do this problem.}

回答2:

Well not a pretty clean implementation but since you need only the children and sub-children, either of these might work:

Query1:

SELECT UserID, UserName
FROM tbl_user
WHERE ParentID = 3 OR UserID = 3
UNION
SELECT UserID, UserName
FROM tbl_user
WHERE ParentID IN (SELECT UserID
FROM tbl_user
WHERE ParentID = 3);

Query 2:

SELECT UserID, UserName
FROM tbl_user 
WHERE UserID = 3
OR ParentID = 3
OR ParentID IN (SELECT UserID
    FROM tbl_user
    WHERE ParentID = 3);

EDIT 1: Alternatively, you may modify your table structure to make it more convenient to query all children of a particular category. Please follow this link to read more on storing hierarchical data in MySQL.

Also, you may think on storing your data hierarchically in a tree-like fashion that is very well explained in this article.

Please note that each method has its trade-offs with respect to retrieving desired results vs adding/removing categories but I'm sure you'll enjoy the reading.

回答3:

This is one of the best articles I've seen for explaining the "Modified Preorder Tree Traversal" method of storing tree-like data in a SQL-style database.

http://www.sitepoint.com/hierarchical-data-database/

The MPTT stuff starts on page 2.

Essentially, you store a "Left" and a "Right" value for each node in the tree, in such a manner that to get all children of ParentA, you get the Left and Right for ParentA, then

SELECT * 
FROM TableName
WHERE Left > ParentLeft 
AND Right < ParentRight

回答4:

To get all of the parents of the selected child (user_id = 3 in this example):

         SELECT
              @parent_id AS _user_id,
              user_name,
              (
                 SELECT @parent_id := parent_id
                 FROM users
                 WHERE user_id = _user_id
              ) AS parent
           FROM (
        -- initialize variables
              SELECT
                 @parent_id := 3
              ) vars,
              users u
           WHERE @parent_id <> 0;

To get all of the children of a selected user_id

SELECT ui.user_id AS 'user_id', ui.user_name AS 'user_name', parent_id,
FROM
(
  SELECT connect_by_parent(user_id) AS user_id
  FROM (
    SELECT
      @start_user := 3,
      @user_id := @start_user
  ) vars, users
  WHERE @user_id IS NOT NULL
  ) uo
JOIN users ui ON ui.user_id = uo.user_id

This requires the following function

CREATE FUNCTION connect_by_parent(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _user_id INT;
        DECLARE _parent_id INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @user_id = NULL;


SET _parent_id = @user_id;
SET _user_id = -1;

IF @user_id IS NULL THEN
        RETURN NULL;
END IF;

LOOP
        SELECT  MIN(user_id)
        INTO    @user_id
        FROM    users
        WHERE   parent_id = _parent_id
                AND user_id > _user_id;

        IF @user_id IS NOT NULL OR _parent_id = @start_with THEN
                RETURN @user_id;
        END IF;

        SELECT  user_id, parent_id
        INTO    _user_id, _parent_id
        FROM    users
        WHERE   user_id = _parent_id;
END LOOP;


END

This example heavily uses session variables which many sql users may be unfamiliar with, so here's a link that may provide some insight: session variables