以树形结构计算公司中的人数

Question

你好，我有一个带有companies_id(PK(和parent_companies_id(FK(列的结构表以及带有companies_id(FK(和people_id(PK(的人表。有些人可以在不同的公司工作。我想计算包括孩子在内的每个公司的数量。

+------------------+-------------------------+
| companies_id(PK) | parent_companies_id(FK) |
+------------------+-------------------------+
|              101 | null                    |
|              102 | 101                     |
|              103 | 101                     |
|              104 | 103                     |
|              105 | 103                     |
|              106 | 104                     |
+------------------+-------------------------+
+---------------+------------------+
| people_id(PK) | companies_id(FK) |
+---------------+------------------+
|           001 |              101 |
|           002 |              102 |
|           003 |              102 |
|           004 |              104 |
|           005 |              105 |
|           006 |              106 |
+---------------+------------------+
CREATE TABLE COMPANY
( Company_ID Integer,
Parent_ID Integer
);
CREATE TABLE PEOPLE
( People_ID VARCHAR(3),
Company_ID Integer
);
INSERT INTO COMPANY VALUES (101,NULL);
INSERT INTO COMPANY VALUES (102,101);
INSERT INTO COMPANY VALUES (103,101);
INSERT INTO COMPANY VALUES (104,103);
INSERT INTO COMPANY VALUES (105,103);
INSERT INTO COMPANY VALUES (106,104);
INSERT INTO PEOPLE VALUES ('001',101);
INSERT INTO PEOPLE VALUES ('002',102);
INSERT INTO PEOPLE VALUES ('003',102);
INSERT INTO PEOPLE VALUES ('004',104);
INSERT INTO PEOPLE VALUES ('005',105);
INSERT INTO PEOPLE VALUES ('006',106);

结果必须是下一个

+--------------+------------+
| companies_id | people_qnt |
+--------------+------------+
|          101 |          6 |
|          102 |          2 |
|          103 |          3 |
|          104 |          2 |
|          105 |          1 |
|          106 |          1 |
+--------------+------------+

Answer 1

我在这里假设您的structure表(我称之为"结构"(对每个公司都有一行。如果不是这样，您应该为驱动表使用不同的东西。

select struct.companies_id, count(people_id)
from struct
inner join people on people.companies_id in 
(select s2.companies_id
from struct s2
start with s2.companies_id = struct.companies_id
connect by prior s2.companies_id = s2.parent_companies_id)
group by struct.companies_id
order by 1
;

Sqlfiddle