Three tables, for example,
A grandpa
Son of b
C grandson
A grandpa son also have more than one son has multiple

The query according to the grouping grandpa son how many, how many grandchildren as follows

Grandpa total name son so
Zhang SAN May 15
Li si 8 and 24

CodePudding user response:

Here, there is no primary key, can also be the only value,
SELECT table grandpa. Grandpa, COUNT (DISTINCT son table. The primary key) son, total COUNT (DISTINCT grandchildren tables. The primary key) total grandson FROM grandpa table
Son son LEFT the JOIN table ON table and grandpa table of correlation, multiple correlation with the and separated,
Grandson LEFT the JOIN table ON son table and the table of correlation and multiple correlation with the and separated,
Table GROUP BY grandpa. Grandpa;

CodePudding user response:

reference 1st floor AHUA1001 response:

, there is no primary key, has a unique value can also be,
SELECT table grandpa. Grandpa, COUNT (DISTINCT son table. The primary key) son, total COUNT (DISTINCT grandchildren tables. The primary key) total grandson FROM grandpa table
Son son LEFT the JOIN table ON table and grandpa table of correlation, multiple correlation with the and separated,
Grandson LEFT the JOIN table ON son table and the table of correlation and multiple correlation with the and separated,
Table GROUP BY grandpa. Grandpa;

Not so sure, count the result is the same

CodePudding user response:

refer to the second floor wklilong response:

Quote: refer to 1st floor AHUA1001 response:

, there is no primary key, has a unique value can also be,
SELECT table grandpa. Grandpa, COUNT (DISTINCT son table. The primary key) son, total COUNT (DISTINCT grandchildren tables. The primary key) total grandson FROM grandpa table
Son son LEFT the JOIN table ON table and grandpa table of correlation, multiple correlation with the and separated,
Grandson LEFT the JOIN table ON son table and the table of correlation and multiple correlation with the and separated,
Table GROUP BY grandpa. Grandpa;

Not so sure, count result same

Are you think the result is the same, or to perform after the result is the same,

CodePudding user response:

If the result is the same, please put the execution of the statements made by,

CodePudding user response:

reference 4 floor AHUA1001 response:

if the result is the same, please put the issue to execute statements,

Sorry I write wrong the first time join the wrong field, you can method, is the query speed a bit slower took 300 seconds, how to optimize? thank you

CodePudding user response:

First, grandpa table, behind the where condition, if you have, add index,
Second, table son and grandson table, behind ON the condition of increasing index,