如何实现发票金额按关联AccountCode均分且总额与原金额一致？

现有如下样本数据，需求为将发票行按AccountCode拆分为多行，并计算拆分金额：若关联2个AccountCode则各分50%；若关联3个则拆分后两个为33.33，一个为33.34，以此类推，确保拆分金额总和与原发票金额完全相等。

样本数据SQL #

Create Table #Invoices
(
    InvoiceID int,
    Amount money,
    ProjectID int
)   
insert into #Invoices
(
    InvoiceID,
    Amount,
    ProjectID
)
select
    1,
    100,
    1
union all
select
    2,
    100,
    2
union all
select
    3,
    100,
    3
union all
select
    4,
    100,
    4

Create Table #Projects
(
    ProjectID int,
    AccountCode int
)
insert into #Projects
(
    ProjectID,
    AccountCode
)
select
    1,
    '12345'
union all
select
    2,
    '12345'
union all
select
    2,
    '7890'
union all
select
    3,
    '800'
union all
select
    3,
    '234'
union all
select
    3,
    '987'
union all
select
    4,
    '800'
union all
select
    4,
    '234'
union all
select
    4,
    '987'
union all
select
    4,
    '2579'

现有查询问题 #

当前编写的查询可实现基本拆分，但存在精度问题，例如InvoiceID 3的三个拆分金额（各33.33）总和为99.99，与原金额100不符：

select 
    I.*,
    P.AccountCode,
    I.Amount / count(I.InvoiceID) over (partition by P.ProjectID) as SplitAmount
from 
    #Invoices I
Inner Join #Projects P on 
    I.ProjectID = P.ProjectID
order by
    I.InvoiceID

修改后的SQL查询 #

通过先计算每个项目下的账户总数、给账户分配行号，对非最后一行使用四舍五入的基础拆分金额，最后一行用原金额减去前面所有基础金额的总和，保证总金额与原发票一致：

WITH ProjectAccounts AS (
    SELECT 
        P.ProjectID,
        P.AccountCode,
        COUNT(*) OVER (PARTITION BY P.ProjectID) AS AccountCount,
        ROW_NUMBER() OVER (PARTITION BY P.ProjectID ORDER BY P.AccountCode) AS RowNum
    FROM #Projects P
)
SELECT 
    I.InvoiceID,
    I.Amount,
    I.ProjectID,
    PA.AccountCode,
    CASE
        WHEN PA.RowNum <> PA.AccountCount THEN ROUND(I.Amount / PA.AccountCount, 2)
        ELSE I.Amount - (ROUND(I.Amount / PA.AccountCount, 2) * (PA.AccountCount - 1))
    END AS SplitAmount
FROM #Invoices I
JOIN ProjectAccounts PA ON I.ProjectID = PA.ProjectID
ORDER BY I.InvoiceID, PA.RowNum

期望结果 #

内容的提问来源于stack exchange，提问作者Philip