从树状结构中MySQL查询计数键

Question

我有三张桌子：

 Department(ID,name) , 
 SubDepartment(parentID,childID) and 
 Department_Employees(idEmployee, idDepartment)

1. 在表"Department_Employees“idDepartment中，外键链接到"Department”表的ID。
2. 在表"SubDepartment“parentID和childID中，外键都链接到"Department”表的ID。

SubDepartments(children) One部门(父部门)可以有许多SubDepartments(children)，而这些SubDepartments可以有自己的SubDepartments(children)，但SubDepartment(child)不能是他的任何父部门的父部门。"idEmployee"在表"Department_Employees“(员工只能属于一个部门)中是不同的。这些规则是从前端覆盖的。

我必须数一数雇员("idEmployee")在某些部门和他的SubDepartments中有多少人。我还得数一数在某些部门有多少SubDepartments，包括所有的孩子。

让我们说，我有这样的东西：

 1. MainDepartment(6 employees)



 - ITSector(3 employees)

    * Databases(4 employees)
    * Programmers(2 employees)


 - DeliverySector(4 employees)

我需要输出是MainDepartment，有4 subDepartments和19名员工。

是否可以或建议在一个带有子查询的查询中执行此操作？我想我需要一些递归，但是我不知道怎么写它。

Answer 1

您可以使用嵌套集模型来存储分层数据。这意味着您的表将是形式的：Department(ID,name, parentID, left, right ) and Department_Employees(idEmployee, idDepartment)。

    SELECT COUNT(parent.name), COUNT(employee.id)
FROM Department AS node ,
     Department AS parent,
     Department_employee AS employee
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.idDepartment = product.idDepartment
        AND parent.name = 'MAIN DEPARTMENT';

更多关于分层数据的嵌套集表示的信息

其他选项是保持数据库结构不变。您可以使用子查询或存储过程查找子部门。

select count(id), count(distinct(idDepartment)) from Department_employee 
                   where idDepartment IN ( Getsubdepartment('Department') );

Answer 2

递归听起来像是：

WITH RECURSIVE dep (main_id,child_id,name) as
(SELECT d.id,d.id,d.name 
 FROM department d
 UNION ALL
 SELECT d.main_id, sd.child, d.name || '.' || sd.name
 FROM dep d INNER JOIN subdepartment sd on d.child_id=sd.parentid
)
SELECT s.name,count(idemployee) 
FROM dep d INNER JOIN dep s ON d.name LIKE s.name || '%'
LEFT JOIN department_employees de ON d.child_id=de.iddepartment
GROUP BY s.name
ORDER BY s.name;

在postgres上进行测试，它也应该在mysql中工作。

它使用路径系统存储树。它很好，很容易。左、右不能很好地应用于大树和二叉树，因为在每一种情况下，您都必须更新一半db。如果你制作一个分支的复制粘贴，你将更新记录的百万兆。