mysql - 我有一个 mysql 查询一直在处理但无法使其工作

Question

introID是将某个成员(member)引入该计划的成员(member)ID。原始提取数据:

ID  | parentID | introID | name
45       15        12      Rasika Bedekar   
41       14        10      Arjun Rampal 
29       10        8       Raju Aswani  
32       11        8       Sheetal Bhadra   
47       16        8       Luna Verma   
50       17        8       Vinod Gala   
68       23        8       Vibha Palte  
53       18        5       Rashid Khan  
54       18        5       Irfan Pathan
1        0         0       Manish Shah  
2        1         0       Nirmal Malik

构建第一个报告，我们通过以下查询获取每个级别的所有成员:

`select a.ID, a.parentID, a.name,(select count(*) from members where introID = a.ID) as     intro,
count(distinct b.ID) as level1,
count(distinct c.ID) as level2,
count(distinct d.ID) as level3,
count(distinct e.ID) as level4,
count(distinct f.ID) as level5,
count(distinct g.ID) as level6,
count(distinct h.ID) as level7,
count(distinct i.ID) as level8,
count(distinct j.ID) as level9,
count(distinct k.ID) as level10
from members a
left join members b on b.parentID = a.ID
left join members c on c.parentID = b.ID
left join members d on d.parentID = c.ID
left join members e on e.parentID = d.ID
left join members f on f.parentID = e.ID
left join members g on g.parentID = f.ID
left join members h on h.parentID = g.ID
left join members i on i.parentID = h.ID
left join members j on j.parentID = i.ID
left join members k on k.parentID = j.ID
left join members l on l.parentID = k.ID
where a.ID IN (select ID from members) group by ID`

结果是这样的:

ID | parentID | name | totals_intro | level1 | level2 | level3 | level4 |
1       0       name1        0           3       9        27       81
2       1       name2        0           3       9        27       36
3       1       name3        0           3       9        27       0
4       1       name4        0           3       9        27       0
5       2       name5        2           3       9        27       0

问题，如何获取每个级别的totals_intro？ level1_intro、level2_itro 等等...每个级别的结果是在下一个级别 introID 中查找的 ID 的集合

Answer 1

我有一个答案，并遵循邻接列表模型，其他人知道如何构建查询:

select a.ID, a.name, (select count(*) from members where introID = a.ID) as Total_introduced, 
count(distinct b.ID) as level1,
count(distinct c.ID) as level2,
count(distinct d.ID) as level3,
count(distinct e.ID) as level4,
count(distinct f.ID) as level5, 
count(distinct g.ID) as level6,
count(distinct h.ID) as level7,
count(distinct i.ID) as level8,
count(distinct j.ID) as level9,
count(distinct k.ID) as level10,
COUNT(distinct IF(b.introID = a.ID, b.ID, NULL)) AS I1,
COUNT(distinct IF(c.introID = a.ID, c.ID, NULL)) AS I2,
COUNT(distinct IF(d.introID = a.ID, d.ID, NULL)) AS I3,
COUNT(distinct IF(e.introID = a.ID, e.ID, NULL)) AS I4,
COUNT(distinct IF(f.introID = a.ID, f.ID, NULL)) AS I5,
COUNT(distinct IF(g.introID = a.ID, g.ID, NULL)) AS I6,
COUNT(distinct IF(h.introID = a.ID, h.ID, NULL)) AS I7,
COUNT(distinct IF(i.introID = a.ID, i.ID, NULL)) AS I8,
COUNT(distinct IF(j.introID = a.ID, j.ID, NULL)) AS I9,
COUNT(distinct IF(k.introID = a.ID, k.ID, NULL)) AS I10
from members a
left join members b on b.parentID = a.ID 
left join members c on c.parentID = b.ID 
left join members d on d.parentID = c.ID 
left join members e on e.parentID = d.ID 
left join members f on f.parentID = e.ID
left join members g on g.parentID = f.ID
left join members h on h.parentID = g.ID
left join members i on i.parentID = h.ID
left join members j on j.parentID = i.ID
left join members k on k.parentID = j.ID
left join members l on l.parentID = k.ID
where a.ID IN (select ID from members) group by ID