php - [id pid] type table data, obtain the number of all sub-teams for each id, the data volume is more than 2W.

Question

There is a data table of type [id pid], as follows:

--- id --- pid ---
--- A --- B ---
--- A --- C ---
--- B --- D ---
--- B --- E ---
--- C --- F ---
--- C --- G ---

Corresponding structure:

-D
        -E
    -B
A
    -C
        -F
        - G

How to count the number of subordinate teams of A, B, C, D, E, F, G (including yourself).
For example

id number of teams
A 7
B 3
C 3
D 1
E 1
F 1
G 1

Currently, I take out all the data and obtain such data through recursion. If there are subordinates, I count the number of teams recursively.

Problems encountered:
When the data volume reaches 2W, all data is taken out, causing the memory to be full
Do you have any good solutions?

Answer 1

Counting step by step, there is no need to take out the entire tree at once;
In addition, the path can be recorded for each node; for statistics, just count (*) like '%A%', for example, to find the number of descendants of A