mysql - 如何在 mysql 上按 ID 分组并仅计算 5 的值？

Question

我有一个 mysql 查询，它提供了 ID = 5 时的所有子元素，现在我想按 ID 对结果进行分组，并计算每个级别有多少个元素 = 5

`SELECT t1.ID, t1.name,
t1.ID as level1, t1.introID as i1, 
t2.ID as level2, t2.introID as i2,
t3.ID as level3, t3.introID as i3,
t4.ID as level4, t4.introID as i4,
t5.ID as level5, t5.introID as i5,
t6.ID as level6, t6.introID as i6,
t7.ID as level7, t7.introID as i7,
t8.ID as level8, t8.introID as i8,
t9.ID as level9, t9.introID as i9, 
t10.ID as level10, t10.introID as i10
FROM members AS t1
LEFT JOIN members AS t2 ON t2.parentID = t1.ID
LEFT JOIN members AS t3 ON t3.parentID = t2.ID
LEFT JOIN members AS t4 ON t4.parentID = t3.ID
LEFT JOIN members AS t5 ON t5.parentID = t4.ID
LEFT JOIN members AS t6 ON t6.parentID = t5.ID
LEFT JOIN members AS t7 ON t7.parentID = t6.ID
LEFT JOIN members AS t8 ON t8.parentID = t7.ID
LEFT JOIN members AS t9 ON t9.parentID = t8.ID
LEFT JOIN members AS t10 ON t10.parentID = t9.ID
LEFT JOIN members AS t11 ON t11.parentID = t10.ID
WHERE t1.ID = 5
group by t2.id, t3.id, t4.id, t5.id, t6.id, t7.id, t8.id, t9.id, t10.id`

这会产生这样的结果:

    ID  NAME        LEVEL1      I1   LEVEL2 I2  LEVEL3  I3  LEVEL4  I4
    5   Vina Malik  5           0       14  0   41      0   122     0                                               
    5   Vina Malik  5           0       14  0   41      0   123     0                                               
    5   Vina Malik  5           0       14  0   41      0   124     0                                               
    5   Vina Malik  5           0       14  0   42      0   125     0                                               
    5   Vina Malik  5           0       14  0   42      0   126     0                                               
    5   Vina Malik  5           0       14  0   42      0   127     0                                               
    5   Vina Malik  5           0       14  0   43      0   128     5                                               
    5   Vina Malik  5           0       14  0   43      0   129     0                                               
    5   Vina Malik  5           0       14  0   43      0   130     0                                               
    5   Vina Malik  5           0       15  0   44      0   131     0                                               
    5   Vina Malik  5           0       15  0   44      0   132     0                                               
    5   Vina Malik  5           0       15  0   44      0   133     0                                               
    5   Vina Malik  5           0       15  0   45      5   134     0                                               
    5   Vina Malik  5           0       15  0   45      5   135     0                                               
    5   Vina Malik  5           0       15  0   45      5   136     5                                               
    5   Vina Malik  5           0       15  0   46      0   137     0                                               
    5   Vina Malik  5           0       15  0   46      0   138     0                                               
    5   Vina Malik  5           0       15  0   46      0   139     0                                               
    5   Vina Malik  5           0       16  0   47      0   140     0                                               
    5   Vina Malik  5           0       16  0   47      0   141     0                                               
    5   Vina Malik  5           0       16  0   47      0   142     0                                               
    5   Vina Malik  5           0       16  0   48      0   143     5                                               
    5   Vina Malik  5           0       16  0   48      0   144     0                                               
    5   Vina Malik  5           0       16  0   48      0   145     0                                               
    5   Vina Malik  5           0       16  0   49      5   146     0                                               
    5   Vina Malik  5           0       16  0   49      5   147     0                                               
    5   Vina Malik  5           0       16  0   49      5   148     0                                               

我想获得每个 i 列中等于 5 的元素计数，并对每个 id 的结果进行分组，然后从 select 中删除 ID，计算每个 i 列中有多少个元素 = 5 的最终标准是这样的，在 i1 和 i2 中没有元素，但在 i3 和 i4 中，我们可以在 LEVEL3 列中看到 introID = 5 的子元素的 ID，在这种情况下，我们可以重复看到元素 45 和 49 的值在 i4 = 5 上，但在计数时可能会得到 6，而实际情况是 I3 列上必须为 2

    ID  NAME        LEVEL1      I1   LEVEL2 I2  LEVEL3  I3  LEVEL4  I4
    5   Vina Malik  5           0       14  0   43      0   128     5                                               
    5   Vina Malik  5           0       15  0   45      5   
    5   Vina Malik  5           0       15  0   45      5   
    5   Vina Malik  5           0       15  0   45      5   136     5                                               
    5   Vina Malik  5           0       16  0   48      0   143     5                                               
    5   Vina Malik  5           0       16  0   48      0   
    5   Vina Malik  5           0       16  0   48      0   
    5   Vina Malik  5           0       16  0   49      5   
    5   Vina Malik  5           0       16  0   49      5   
    5   Vina Malik  5           0       16  0   49      5   

所以最终结果必须是这样的:

    ID  NAME        I1   I2  I3  I4
    5   Vina Malik  0    0   2   3

任何人都可以帮助我对所有内容进行分组和计数以获得所需的结果吗？谢谢

Answer 1

我会回应上面的评论，您可能应该考虑重新设计您的数据库架构。

但是 - 根据您拥有的架构，我相信这个查询会得到您想要的:

SELECT t1.ID, t1.name,
COUNT(distinct IF(t1.introID = 5, t1.ID, NULL)) AS I1,
COUNT(distinct IF(t2.introID = 5, t2.ID, NULL)) AS I2,
COUNT(distinct IF(t3.introID = 5, t3.ID, NULL)) AS I3,
COUNT(distinct IF(t4.introID = 5, t4.ID, NULL)) AS I4,
COUNT(distinct IF(t5.introID = 5, t5.ID, NULL)) AS I5,
COUNT(distinct IF(t6.introID = 5, t6.ID, NULL)) AS I6,
COUNT(distinct IF(t7.introID = 5, t7.ID, NULL)) AS I7,
COUNT(distinct IF(t8.introID = 5, t8.ID, NULL)) AS I8,
COUNT(distinct IF(t9.introID = 5, t9.ID, NULL)) AS I9,
COUNT(distinct IF(t10.introID = 5, t10.ID, NULL)) AS I10
FROM members AS t1
LEFT JOIN members AS t2 ON t2.parentID = t1.ID
LEFT JOIN members AS t3 ON t3.parentID = t2.ID
LEFT JOIN members AS t4 ON t4.parentID = t3.ID
LEFT JOIN members AS t5 ON t5.parentID = t4.ID
LEFT JOIN members AS t6 ON t6.parentID = t5.ID
LEFT JOIN members AS t7 ON t7.parentID = t6.ID
LEFT JOIN members AS t8 ON t8.parentID = t7.ID
LEFT JOIN members AS t9 ON t9.parentID = t8.ID
LEFT JOIN members AS t10 ON t10.parentID = t9.ID
LEFT JOIN members AS t11 ON t11.parentID = t10.ID
WHERE t1.ID = 5
group by t1.id;

您可以在此处查看 SQLFiddle:http://sqlfiddle.com/#!2/3e109/5