SQL每日一题(20201130)T1130B表Quantity的和，平均分给T1130A表的Type，不能存在小数，且平均数相加还要等于Quantity的和

SQL每日一题(20210727)
SQL每日一题(20211020)
SQL每日一题(20220412)

select id,
       type,
       case
           when row_number() over (partition by quantity order by quantity) = 1 then (sm - (cn - 1) * truncate((sm / cn),0))
           else truncate((sm / cn),0) end 平均
from (select ID, Type,  cn, Quantity,  sm
      from (select id, type, dense_rank() over (order by type) dk, count(id) over (partition by type) cn
            from t1130a
            group by type, id
            order by id) a
               left join (select quantity, dense_rank() over (order by quantity desc) dk, sum(quantity) sm
                          from t1130b
                          group by quantity) b on a.dk = b.dk
      order by id desc) a
order by id

select id,
       type,
       case
           when rm = max(rm) over (partition by type) then round(sub_quantity -
                                                                 sub_quantity / max(rm) over (partition by type) *
                                                                 (max(rm) over (partition by type) - 1), 0)
           else sub_quantity / max(rm) over (partition by type) end as av
from (select a.id,
             a.type,
             sum(quantity) over (partition by type)  as sub_quantity,
             row_number() over (partition by a.type) as rm
      from T1130A a
               left join T1130b b on a.id = b.id) c

select T1130A.id,
       T1130A.type,
       case
           when max(T1130A.id) over (PARTITION by type) = T1130A.ID
               then SUM(Quantity) over (PARTITION by type) -
                    (SUM(Quantity) over (PARTITION by type) / count(T1130A.id) over (PARTITION by type) *
                     (count(T1130A.id) over (PARTITION by type) - 1))
           else SUM(Quantity) over (PARTITION by type) / count(T1130A.id) over (PARTITION by type)
           end 平均
from T1130A
         left outer join T1130B on T1130A.id = T1130B.id