如何使用MySQL创建declining balance method查询以计算会计折旧

嘿，针对你这个固定资产折旧的需求，用MySQL的**递归CTE（Common Table Expression）**就能完美实现，不需要复杂的函数，直接一条查询就能生成你要的表格结果。下面是具体的实现步骤和代码：

思路说明 #

余额递减法的核心是每年的折旧额基于上一年度的期末账面价值计算，递归CTE正好能处理这种依赖前序结果的场景：

• 锚点部分：生成第一年的折旧数据，直接使用初始设备成本作为期初账面价值
• 递归部分：从第二年开始，每次引用上一轮的期末账面价值作为当期期初，重复计算折旧额和期末值，直到达到预计使用年限

完整查询代码 #

SET @cost = 16000;       -- 设备初始成本
SET @depreciation_rate = 0.4; -- 年折旧率（40%）
SET @useful_years = 5;    -- 预计使用年限

WITH RECURSIVE depreciation_calc AS (
    -- 锚点成员：第一年的数据
    SELECT
        1 AS year,
        @cost AS book_value_start,
        @depreciation_rate AS rate,
        ROUND(@cost * @depreciation_rate, 2) AS depreciation,
        ROUND(@cost - (@cost * @depreciation_rate), 2) AS book_value_end
    UNION ALL
    -- 递归成员：生成后续年份的数据
    SELECT
        dc.year + 1 AS year,
        dc.book_value_end AS book_value_start,
        @depreciation_rate AS rate,
        ROUND(dc.book_value_end * @depreciation_rate, 2) AS depreciation,
        ROUND(dc.book_value_end - (dc.book_value_end * @depreciation_rate), 2) AS book_value_end
    FROM depreciation_calc dc
    WHERE dc.year < @useful_years
)
SELECT
    year,
    FORMAT(book_value_start, 2) AS book_value_start,
    CONCAT(FORMAT(rate * 100, 0), '%') AS rate,
    FORMAT(depreciation, 2) AS depreciation,
    FORMAT(book_value_end, 2) AS book_value_end
FROM depreciation_calc;

代码解释 #

• SET语句：定义三个变量来存储成本、折旧率和使用年限，方便后续修改参数
• 递归CTE的depreciation_calc：
  • 锚点查询直接计算第一年的所有字段，用初始成本作为期初值
  • 递归查询每次将年份+1，用上一年的book_value_end作为本年的book_value_start，重复折旧计算逻辑
• 最后外层查询用FORMAT()函数把数值格式化为带千分符和两位小数的字符串，和你给出的示例格式完全匹配

执行结果 #

执行后会返回和你示例完全一致的结果：

可选：封装为存储函数 #

如果你希望更方便地复用，可以把逻辑封装成一个存储函数，接受三个参数并返回结果集：

DELIMITER //
CREATE FUNCTION calculate_declining_balance(
    p_cost DECIMAL(10,2),
    p_rate DECIMAL(5,2),
    p_years INT
) RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT;
    WITH RECURSIVE depreciation_calc AS (
        SELECT
            1 AS year,
            p_cost AS book_value_start,
            p_rate AS rate,
            ROUND(p_cost * p_rate, 2) AS depreciation,
            ROUND(p_cost - (p_cost * p_rate), 2) AS book_value_end
        UNION ALL
        SELECT
            dc.year + 1 AS year,
            dc.book_value_end AS book_value_start,
            p_rate AS rate,
            ROUND(dc.book_value_end * p_rate, 2) AS depreciation,
            ROUND(dc.book_value_end - (dc.book_value_end * p_rate), 2) AS book_value_end
        FROM depreciation_calc dc
        WHERE dc.year < p_years
    )
    SELECT GROUP_CONCAT(
        CONCAT(year, '|',
               FORMAT(book_value_start,2), '|',
               CONCAT(FORMAT(rate*100,0),'%'), '|',
               FORMAT(depreciation,2), '|',
               FORMAT(book_value_end,2))
        SEPARATOR '\n'
    ) INTO result FROM depreciation_calc;
    RETURN result;
END //
DELIMITER ;

-- 使用函数
SELECT calculate_declining_balance(16000, 0.4, 5);

这个函数会返回格式化后的文本结果，你也可以根据需求调整返回格式。

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