我已经成功地对子组进行了分组,现在也是这样,我想根据该表中的所有子组进行一些头计数
Head child assigned total
In In1 3 5
In In2 2 3
In In3 2 3
In In4 1 3
In In5 0 4
In In6 4 4
In In7 3 7
In In8 2 3
Ma Ma1 2 5
Ma Ma2 0 5
Usr us1 4 4
Usr us2 1 3
所以,我复制了孩子分配的总列。
Head assigned total child assigned total
In 3 5 In1 3 5
In 2 3 In2 2 3
In 2 3 In3 2 3
In 1 3 In4 1 3
In 0 4 In5 0 4
In 4 4 In6 4 4
In 3 7 In7 3 7
In 2 3 In8 2 3
Ma 2 5 Ma1 2 5
Ma 0 5 Ma2 0 5
Us 4 4 us1 4 4
Us 1 3 us2 1 3
现在我想对父级1进行分组,因此,所有In(In1-In8(的计数都应该是sum,并且在header assignedHead,totalHead 中
Head assigned total child assigned total
In 17 32 In1 3 5
In 17 32 In2 2 3
In 17 32 In3 2 3
In 17 32 In4 1 3
In 17 32 In5 0 4
In 17 32 In6 4 4
In 17 32 In 3 7
In 17 32 In8 2 3
Ma 2 10 Ma1 2 5
Ma 2 10 Ma2 0 5
Us 5 7 us1 4 4
Us 5 7 us2 1 3
或者像这样高效的东西。
您可以进行窗口求和(如果您的RDBMS支持窗口函数(:
select
head,
assigned,
total,
sum(assigned) over(partition by head) sum_assigned,
sum(total) over(partition by head) sum_total
from mytable
DB Fiddle上的演示:
head|assigned|total|sum_assigned|sum_total:-------|-------:|----:|---------:|-------在|3|5|17|32在|2|3|17|32在|2|3|17|32在|1|3|17|32在|0|4|17|32在|4|4|17|32在|3|7|17|32在|2|3|17|32马|2|5|2|10马|0|5|2|10Usr|4|4|5|7Usr|1|3|5|7