你的位置:首页 > 软件开发 > 数据库 > MySQL: Tree

MySQL: Tree

发布时间:2016-07-06 10:00:10
http://dba.stackexchange.com/questions/30021/mysql-tree-hierarchical-query No problem. We wont show you that ad again. Why didnt you ...

 

         http://dba.stackexchange.com/questions/30021/mysql-tree-hierarchical-query 
         up vote13down votefavorite7

SUB-TREE WITHIN A TREE in MySQL

In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).  

Query to Create Table: 

CREATE TABLE IF NOT EXISTS `Employee` ( `SSN` varchar(64) NOT NULL, `Name` varchar(64) DEFAULT NULL, `Designation` varchar(128) NOT NULL, `MSSN` varchar(64) NOT NULL,  PRIMARY KEY (`SSN`), CONSTRAINT `FK_Manager_Employee`        FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have inserted a set of tuples (Query): 

INSERT INTO Employee VALUES ("1", "A", "OWNER", "1"),  ("2", "B", "BOSS",  "1"), # Employees under OWNER ("3", "F", "BOSS",  "1"), ("4", "C", "BOSS",  "2"), # Employees under B ("5", "H", "BOSS",  "2"), ("6", "L", "WORKER", "2"), ("7", "I", "BOSS",  "2"), # Remaining Leaf nodes  ("8", "K", "WORKER", "3"), # Employee under F   ("9", "J", "WORKER", "7"), # Employee under I   ("10","G", "WORKER", "5"), # Employee under H ("11","D", "WORKER", "4"), # Employee under C ("12","E", "WORKER", "4")

The inserted rows has following Tree-Hierarchical-Relationship:    

     A   <---ROOT-OWNER    /|\          / A \       B   F   //| \  \       // | \  K    / | |  \           I L H  C    /   |  / \ J   G D  E

I written a query to find relationship: 

SELECT SUPERVISOR.name AS SuperVisor,     GROUP_CONCAT(SUPERVISEE.name ORDER BY SUPERVISEE.name ) AS SuperVisee,     COUNT(*) FROM Employee AS SUPERVISOR  INNER JOIN Employee SUPERVISEE ON SUPERVISOR.SSN = SUPERVISEE.MSSN GROUP BY SuperVisor;

And output is: 

+------------+------------+----------+| SuperVisor | SuperVisee | COUNT(*) |+------------+------------+----------+| A     | A,B,F   |    3 || B     | C,H,I,L  |    4 || C     | D,E    |    2 || F     | K     |    1 || H     | G     |    1 || I     | J     |    1 |+------------+------------+----------+6 rows in set (0.00 sec)

[QUESTION] Instead of complete Hierarchical Tree, I need a SUB-TREE from a point (selective) e.g.: If input argument is B then output should be as below...

+------------+------------+----------+| SuperVisor | SuperVisee | COUNT(*) |+------------+------------+----------+| B     | C,H,I,L  |    4 || C     | D,E    |    2 || H     | G     |    1 || I     | J     |    1 |+------------+------------+----------+  

Please help me on this. If not query, a stored-procedure can be helpful. I tried, but all efforts were useless!

        asked Dec 6 '12 at 15:36         
         up vote2down voteaccepted        answered Dec 10 '12 at 20:09         
         up vote2down vote    

原标题:MySQL: Tree

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。