递归mysql选择? [英] Recursive mysql select?

问题描述

我看到了这个答案，我希望他是错误的，就像有人不正确地告诉主键在列上一样而且我不能在多列上设置它.

I saw this answer and i hope he is incorrect, just like someone was incorrect telling primary keys are on a column and I can't set it on multiple columns.

这是我的桌子

create table Users(id INT primary key AUTO_INCREMENT,
    parent INT,
    name TEXT NOT NULL,
    FOREIGN KEY(parent)
    REFERENCES Users(id)
);


+----+--------+---------+
| id | parent | name    |
+----+--------+---------+
|  1 |   NULL | root    |
|  2 |      1 | one     |
|  3 |      1 | 1down   |
|  4 |      2 | one_a   |
|  5 |      4 | one_a_b |
+----+--------+---------+

我想选择用户ID 2并递归，以便获得其所有直接子对象和间接子对象(即ID 4和5).

I'd like to select user id 2 and recurse so I get all its direct and indirect child (so id 4 and 5).

如何以一种可行的方式编写它?我在postgresql和sqlserver中看到了递归.

How do I write it in such a way this will work? I seen recursion in postgresql and sqlserver.

推荐答案

CREATE DEFINER = 'root'@'localhost'
PROCEDURE test.GetHierarchyUsers(IN StartKey INT)
BEGIN
  -- prepare a hierarchy level variable 
  SET @hierlevel := 00000;

  -- prepare a variable for total rows so we know when no more rows found
  SET @lastRowCount := 0;

  -- pre-drop temp table
  DROP TABLE IF EXISTS MyHierarchy;

  -- now, create it as the first level you want... 
  -- ie: a specific top level of all "no parent" entries
  -- or parameterize the function and ask for a specific "ID".
  -- add extra column as flag for next set of ID's to load into this.
  CREATE TABLE MyHierarchy AS
  SELECT U.ID
       , U.Parent
       , U.`name`
       , 00 AS IDHierLevel
       , 00 AS AlreadyProcessed
  FROM
    Users U
  WHERE
    U.ID = StartKey;

  -- how many rows are we starting with at this tier level
  -- START the cycle, only IF we found rows...
  SET @lastRowCount := FOUND_ROWS();

  -- we need to have a "key" for updates to be applied against, 
  -- otherwise our UPDATE statement will nag about an unsafe update command
  CREATE INDEX MyHier_Idx1 ON MyHierarchy (IDHierLevel);


  -- NOW, keep cycling through until we get no more records
  WHILE @lastRowCount > 0
  DO

    UPDATE MyHierarchy
    SET
      AlreadyProcessed = 1
    WHERE
      IDHierLevel = @hierLevel;

    -- NOW, load in all entries found from full-set NOT already processed
    INSERT INTO MyHierarchy
    SELECT DISTINCT U.ID
                  , U.Parent
                  , U.`name`
                  , @hierLevel + 1 AS IDHierLevel
                  , 0 AS AlreadyProcessed
    FROM
      MyHierarchy mh
    JOIN Users U
    ON mh.Parent = U.ID
    WHERE
      mh.IDHierLevel = @hierLevel;

    -- preserve latest count of records accounted for from above query
    -- now, how many acrual rows DID we insert from the select query
    SET @lastRowCount := ROW_COUNT();


    -- only mark the LOWER level we just joined against as processed,
    -- and NOT the new records we just inserted
    UPDATE MyHierarchy
    SET
      AlreadyProcessed = 1
    WHERE
      IDHierLevel = @hierLevel;

    -- now, update the hierarchy level
    SET @hierLevel := @hierLevel + 1;

  END WHILE;


  -- return the final set now
  SELECT *
  FROM
    MyHierarchy;

-- and we can clean-up after the query of data has been selected / returned.
--    drop table if exists MyHierarchy;


END

它看起来很麻烦，但是要使用它，做

It might appear cumbersome, but to use this, do

call GetHierarchyUsers( 5 );

(或您要在层次树中查找的任何密钥ID).

(or whatever key ID you want to find UP the hierarchical tree for).

前提是从您正在使用的一个钥匙开始.然后，将其用作加入用户表AGAIN的基础，但要基于第一个条目的PARENT ID.找到后，将其更新为临时表，以免在下一个周期再次尝试加入该键.然后继续操作，直到找不到更多的父" ID密钥为止.

The premise is to start with the one KEY you are working with. Then, use that as a basis to join to the users table AGAIN, but based on the first entry's PARENT ID. Once found, update the temp table as to not try and join for that key again on the next cycle. Then keep going until no more "parent" ID keys can be found.

无论嵌套的深度如何，这都会将整个记录层次结构返回到父级.但是，如果只需要FINAL父级，则可以使用@hierlevel变量仅返回添加的文件中的最后一个，或者使用ORDER BY和LIMIT 1

This will return the entire hierarchy of records up to the parent no matter how deep the nesting. However, if you only want the FINAL parent, you can use the @hierlevel variable to return only the latest one in the file added, or ORDER BY and LIMIT 1

这篇关于递归mysql选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！