MySQL如何找到相对于父级的子行总数 [英] mysql how to find the total number of child rows with respect to a parent

问题描述

我有一张桌子，上面有这样的亲子关系，

I have a table which having parent child relatiionship like this,

Employee_ID     |  Employee_Manager_ID   |  Employee_Name
--------------------------------------------------------
1               |  1                    |  AAAA
2               |  1                    |  BBBB
3               |  2                    |  CCCC
4               |  3                    |  DDDD
5               |  3                    |  EEEEE

是否可以通过单个查询获得所有雇员的总数(不只是直属子女，而是所有子子女的总数)?

Is it possible to get the count of all the employees come under a particular employee(Not only direct child,count of all the childs of child ) using a single query ?

Eg if the input  = 1
output should be 4

if input  = 2 ,output should be  3

预先感谢

推荐答案

假设您的表是:

mysql> SELECT * FROM Employee;
    +-----+------+-------------+------+
    | SSN | Name | Designation | MSSN |
    +-----+------+-------------+------+
    | 1   | A    | OWNER       | 1    |
    | 10  | G    | WORKER      | 5    |
    | 11  | D    | WORKER      | 5    |
    | 12  | E    | WORKER      | 5    |
    | 2   | B    | BOSS        | 1    |
    | 3   | F    | BOSS        | 1    |
    | 4   | C    | BOSS        | 2    |
    | 5   | H    | BOSS        | 2    |
    | 6   | L    | WORKER      | 2    |
    | 7   | I    | BOSS        | 2    |
    | 8   | K    | WORKER      | 3    |
    | 9   | J    | WORKER      | 7    |
    +-----+------+-------------+------+
    12 rows in set (0.00 sec)

查询是:

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;

查询将产生如下结果:

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

[答案]: 这对于一个级别(立即监督)可以找到您必须在while循环中使用的所有可能级别的所有监督(使用存储过程).

[Answer]: This for One level (immediate supervise) to find all supervises at all possible level you have to use while loop (use stored procedures).

尽管可以在每个级别上检索雇员然后采用他们的UNION，但是通常情况下，我们不能在不使用循环机制的情况下指定诸如在所有级别上检索雇员的受管理者"之类的查询. "

Although it is possible to retrieve employees at each level and then take their UNION, we cannot, in general, specify a query such as "retrieve the supervisees of a employee at all levels" without utilizing a looping mechanism."

参考:在此幻灯片中读取幻灯片编号23. 该书是关系代数和关系微积分"一章中的"FourthEdition数据库系统基础知识"，主题为递归封闭运算".

REFERENCE: in this slide read slid number 23. The BOOK is " FUNDAMENTALS OF FourthEdition DATABASE SYSTEMS" in chapter "The Relational Algebra and Relational Calculus" there is a topic "Recursive Closure Operations".

添加查询以创建表，可能对您有帮助:

Adding Query for Table creation, May be helpful to you:

mysql> 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;
Query OK, 0 rows affected (0.17 sec)

您可以像这样检查Table:

You can check Table like:

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| Name        | varchar(64)  | YES  |     | NULL    |       |
| Designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | NO   | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)  

这篇关于MySQL如何找到相对于父级的子行总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！