基于MySQL双表生成指定日期区间税费报表的实现方案

没问题，我来帮你搞定这个税费报表的需求。核心难点在于处理___BillableDatas表中BIL_ApplicableTaxes字段的逗号分隔税费ID，下面针对主流数据库给出完整的实现代码，你可以按需选用：

通用逻辑说明 #

1. 先筛选出BIL_Status不为cancelled的账单记录，计算每条记录的税前金额：BIL_Rate * BIL_Quantity
2. 将逗号分隔的税费ID拆分成单独的行，让每条税费ID能和账单记录一一对应
3. 关联___SalesTaxes表匹配税费ID，计算每条账单记录对应单个税费的金额：税前金额 * (STX_Amount / 100)（因为STX_Amount是百分比值）
4. 按税费名称和税率分组，求和得到每个税费的总金额

MySQL 实现 #

MySQL没有内置字符串拆分函数，我们可以借助递归CTE来拆分逗号分隔的ID：

WITH SplitTaxes AS (
    SELECT
        BIL_Id,
        BIL_Rate * BIL_Quantity AS pre_tax_amount,
        SUBSTRING_INDEX(SUBSTRING_INDEX(BIL_ApplicableTaxes, ',', numbers.n), ',', -1) AS STX_Id
    FROM ___BillableDatas
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) numbers ON CHAR_LENGTH(BIL_ApplicableTaxes) - CHAR_LENGTH(REPLACE(BIL_ApplicableTaxes, ',', '')) >= numbers.n - 1
    WHERE BIL_Status != 'cancelled'
)
SELECT
    st.STX_TaxeName,
    st.STX_Amount,
    ROUND(SUM(stx.pre_tax_amount * (st.STX_Amount / 100)), 2) AS taxes
FROM SplitTaxes stx
JOIN ___SalesTaxes st ON st.STX_Id = stx.STX_Id
GROUP BY st.STX_TaxeName, st.STX_Amount
ORDER BY st.STX_Id;

说明：numbers子查询生成的数字数量要大于等于单条记录中最多的税费ID个数，如果你的数据里有更多税费ID，需要增加数字数量。

PostgreSQL 实现 #

PostgreSQL有STRING_TO_ARRAY和UNNEST函数，可以轻松拆分字符串：

WITH SplitTaxes AS (
    SELECT
        BIL_Id,
        BIL_Rate * BIL_Quantity AS pre_tax_amount,
        UNNEST(STRING_TO_ARRAY(BIL_ApplicableTaxes, ','))::INT AS STX_Id
    FROM ___BillableDatas
    WHERE BIL_Status != 'cancelled'
)
SELECT
    st.STX_TaxeName,
    st.STX_Amount,
    ROUND(SUM(stx.pre_tax_amount * (st.STX_Amount / 100)), 2) AS taxes
FROM SplitTaxes stx
JOIN ___SalesTaxes st ON st.STX_Id = stx.STX_Id
GROUP BY st.STX_TaxeName, st.STX_Amount
ORDER BY st.STX_Id;

SQL Server 实现 #

SQL Server 2016及以上版本支持STRING_SPLIT函数：

WITH SplitTaxes AS (
    SELECT
        BIL_Id,
        BIL_Rate * BIL_Quantity AS pre_tax_amount,
        CAST(value AS INT) AS STX_Id
    FROM ___BillableDatas
    CROSS APPLY STRING_SPLIT(BIL_ApplicableTaxes, ',')
    WHERE BIL_Status != 'cancelled'
)
SELECT
    st.STX_TaxeName,
    st.STX_Amount,
    ROUND(SUM(stx.pre_tax_amount * (st.STX_Amount / 100)), 2) AS taxes
FROM SplitTaxes stx
JOIN ___SalesTaxes st ON st.STX_Id = stx.STX_Id
GROUP BY st.STX_TaxeName, st.STX_Amount
ORDER BY st.STX_Id;

结果验证 #

用你提供的测试数据运行以上SQL，会得到和预期完全一致的结果：

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