KeyId varchar (50)
DepartName varchar (200)
ParentId varchar (50)
Data:
AA001 orgnaization of total root
AB001 division root
AA002 administration AA001
AB002 customer-service AB001
AB003 r&d AB001
AB004 AB002 support department
Staff table people
KeyId int
DepartID varchar (50) department
Data:
1 AA001
2 AB001
3 AA002
4 AB002
5 AB003
6 AB004
7 AB004
O statistics the number of departments, the results are as follows:
Orgnaization of AA001 total 2
AA002 administration 1
AB001 division 6
AB002 Tibet. 2
AB003 r&d 1
2 AB004 support department
CodePudding user response:
The mysql database, don't be a stored procedureCodePudding user response:
There is a parent ID parentId department will calculate the number of the above in the parent departmentCodePudding user response:
This is to write a function to realize,
Give you an example for your reference:
Use world;
Int the create table if not exists TB (id, name varchar (50), pid, int sort int, the parent varchar (100), the child varchar (100));
Insert into TB
Values
(1, 'fruit' 0, null, null, null),
(2, 'tropical fruit, 1, null, null, null),
(3, 2 'pineapple', null, null, null),
(4, 'banana', 2, null, null, null),
Pineapple (5, 'South America', 3, null, null, null);
The drop function if the exists getchildlist;
/*
1.
Is originally want to use while found_rows () & gt; 0 to judge,
Because the select group_concat (id) into strChildT from TB where find_in_set (pid, strChildT)
If group_concat (id) is null, also returns 1, so changed to while strChildT is not null.
*/
The CREATE DEFINER=` root ` @ % ` ` FUNCTION ` getChildList ` (idd int) RETURNS a varchar (1000) CHARSET utf8
READS the SQL DATA
The begin
Declare strT varchar (1000);
Declare strChildT varchar (1000);
The set strT='$'; Initialization/* */
The set strChildT=cast (idd as char);/* all things need to find the child nodes of the parent node will put there */
While strChildT is not null do
The set strT=concat (, strChildT strT, ', ');/* for the first time is: $, idd */
The select group_concat (id) into strChildT from TB where find_in_set (pid, strChildT);
End the while;
Return strT./* return: idd all child nodes of commas string, containing idd node itself */
End
- 2. Display the current list of all child node id
Mysql> Select *, getChildList (id) from TB;
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | | id name | pid sort | parent child | | getChildList (id) |
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
Fruit | 1 | | 0 | NULL | NULL | NULL | $, 1, 2, 3, 4, 5 |
| 2 | tropical fruit | | NULL | NULL | NULL | $1, 2, 5-tetrafluorobenzoic |
Pineapple | 3 | | | NULL | NULL | NULL | $2, 3, 5 |
4 | | | 2 | NULL banana | NULL | NULL | $, 4 |
Pineapple | 3 | | | 5 South America NULL | NULL | NULL | $, 5 |
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
5 rows in the set (0.03 SEC)
- 3. 1 the following query node
all child nodesMysql> Select * from TB where find_in_set (id, getChildList (1));
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id name | | pid sort | parent child | |
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
Fruit | 1 | | 0 | NULL | NULL | NULL |
Tropical fruit | 2 | | 1 | NULL | NULL | NULL |
Pineapple | 3 | | 2 | NULL | NULL | NULL |
4 | | | 2 | NULL banana | NULL | NULL |
Pineapple | 3 | | | 5 South America NULL | NULL | NULL |
+ + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
5 rows in the set (0.16 SEC)
CodePudding user response:
MYSQL only with a SQL statement cannot achieve, only by the stored procedure/function, or in the external program implementation,http://blog.csdn.net/acmain_chm/article/details/4142971
Tree all child nodes in the MySQL query
In Oracle, we know that there is a Hierarchical Queries through CONNECT BY we can easily find all the all child nodes under the current node, but it's a pity that in the current version of MySQL has not the function of the corresponding, if it is limited in MySQL level, such as in advance if we can determine the maximum depth of the tree is four, then all nodes in the depth of the root of the tree can't more than 4, then we can directly through the left the join to do this, but many times we...
CodePudding user response:
Upstairs positive solution, pure SQL make uncertain, can only use processCodePudding user response: