Geek asks and answers

MySQL group by least common parent id

I have one table containing ID, Amountand Parent_ID. (This is actually a little more complicated, but these are all important columns for this example). Basically, what I want to do is group and sum the Sum column by the lowest common parent (i.e. those with a parent identifier of NULL).

ID  Amount  Parent_ID
1   100     NULL
2   150     1
3   50      1
4   75      3
5   25      4
6   125     NULL
7   50      6
8   50      7
9   100     8

Expected results:

ID  SUM
1   400
6   325

As you can see, it only reconfigures the two entries, those who do not have a parent, which means that they are top-level elements. The Sum column is the sum of all its children Amountrecursively, therefore ID= 1 is the sum of 1,2,3,4 and 5. and ID= 6 is the sum of 6,7, 8 and 9.

+3
source share
2

, :

select
    coalesce(t5.ID,t4.ID,t3.ID,t2.ID,t1.ID) as Root,
    sum(t1.Amount) as Amount
from Table1 t1
left join Table1 t2 on t1.Parent_ID = t2.ID
left join Table1 t3 on t2.Parent_ID = t3.ID
left join Table1 t4 on t3.Parent_ID = t4.ID
left join Table1 t5 on t4.Parent_ID = t5.ID
group by Root

left join coalesce.

: http://www.sqlfiddle.com/#!2/b7a79/17

+6

, , , -, , .

, . , . , , GROUP BY.

0

More articles:


All Articles