Access编写SQL查询计算部门绩效较前一参考日期的变化百分比

基础查询实现（默认取最新参考日期，筛选变化幅度超5%的部门） #

SELECT 
    t1.dep_id AS 部门ID,
    t1.ref_date AS 当前参考日期,
    t1.val AS 当前绩效值,
    t2.val AS 上一期绩效值,
    ROUND((t1.val - t2.val)/t2.val * 100, 2) AS 绩效变化百分比
FROM 
    tblmain AS t1
    INNER JOIN tblmain AS t2 
    ON t1.dep_id = t2.dep_id
    AND t2.ref_date = (
        SELECT MAX(ref_date) 
        FROM tblmain 
        WHERE dep_id = t1.dep_id AND ref_date < t1.ref_date
    )
WHERE 
    -- 过滤变化幅度超过5%的记录
    ABS((t1.val - t2.val)/t2.val) > 0.05
    -- 固定取最新的参考日期，不需要可以删除此行
    AND t1.ref_date = (SELECT MAX(ref_date) FROM tblmain)
    -- 避免除零错误，建议保留
    AND t2.val <> 0
ORDER BY 
    ABS((t1.val - t2.val)/t2.val) DESC;

逻辑说明 #

1. 通过自关联将当期数据（t1）和同部门的上期数据（t2）匹配
2. 子查询SELECT MAX(ref_date) FROM tblmain WHERE dep_id = t1.dep_id AND ref_date < t1.ref_date用来自动匹配每个部门早于当前参考日期的最近一期日期，不需要手动指定部门ID
3. 变化率计算逻辑为(当期绩效-上期绩效)/上期绩效，取绝对值判断是否超出阈值

适配表单自定义参数版本 #

如果后续要接入表单选择参考日期和变化幅度阈值，Access支持直接引用表单控件值，假设你的表单名为绩效查询表单，包含两个控件：

• 参考日期选择控件名：sel_ref_date
• 变化幅度阈值输入控件名：sel_threshold（输入值为百分比数值，比如输入5代表阈值5%）

只需要修改WHERE部分的条件即可：

WHERE 
    ABS((t1.val - t2.val)/t2.val) > [Forms]![绩效查询表单]![sel_threshold]/100
    AND t1.ref_date = [Forms]![绩效查询表单]![sel_ref_date]
    AND t2.val <> 0

注意事项 #

如果你的ref_date字段存储为文本格式（dd.mm.yyyy）而非日期格式，需要在日期比较前转换格式，将所有用到ref_date的地方替换为CDate(Replace(ref_date, ".", "-"))即可。

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