with TableA as ( select 1 idA , 30 value union all select 2 idA , 20 value ) ,
TableB as ( select 1 idA , 10 idB , 15 value union all select 1 idA , 20 idB , 20 value union all select 2 idA , 30 idB , 5 value union all select 2 idA , 40 idB , 5 value union all select 2 idA , 50 idB , 5 value union all select 2 idA , 60 idB , 10 value )
select b.idA , b.idB , b.value , a.value / COUNT(*) over ( partition by b.idA ) [구하고자하는값] , b.value - ( a.value / COUNT(*) over ( partition by b.idA ) ) [나머지] from TableB b join TableA a on a.idA = b.idA order by b.idA ,b.idB
2008이시면
with
TableA as
(
select 1 idA , 30 value union all
select 2 idA , 20 value
) ,
TableB as
(
select 1 idA , 10 idB , 15 value union all
select 1 idA , 20 idB , 20 value union all
select 2 idA , 30 idB , 5 value union all
select 2 idA , 40 idB , 5 value union all
select 2 idA , 50 idB , 5 value union all
select 2 idA , 60 idB , 10 value
)
select b.idA , b.idB , b.value
, a.value / COUNT(*) over ( partition by b.idA ) [구하고자하는값]
, b.value - ( a.value / COUNT(*) over ( partition by b.idA ) ) [나머지]
from TableB b join TableA a on a.idA = b.idA
order by b.idA ,b.idB