SQLite新手入门：计算军费增速时缺失年份取前值的实现方法

1. SQL 的行过滤逻辑需写在 WHERE 子句中，IF/ELSE 属于流程控制语句，无法直接用于单查询的行筛选场景
2. 原查询存在两处隐性逻辑错误：
   • 增长率公式写反，正确公式为 (末期值-基期值)/基期值*100，原写法会得到反向的增长率结果
   • ORDER BY 后引用的 differ 字段未定义，需替换为实际返回的增长率字段

核心逻辑是给每个国家筛选出 <=2020 年的最新有效军费数据（军费>0），使用窗口函数实现最简洁：

WITH start as (
SELECT
    country_id,
    military_spending as start_spending
FROM military
WHERE year = 2013
    AND military_spending > 0 -- 保证基期数据有效
),
end as (
SELECT
    country_id,
    year as end_year,
    military_spending as end_spending
FROM (
    SELECT
        country_id,
        year,
        military_spending,
        -- 按国家分组，年份倒序排列，最新数据排序为1
        ROW_NUMBER() OVER(PARTITION BY country_id ORDER BY year DESC) as rn
    FROM military
    WHERE year <= 2020
        AND military_spending > 0 -- 过滤无效军费数据
) t
WHERE rn = 1 -- 取每个国家的最新有效数据
)
SELECT
    s.country_id,
    c.name as Country,
    e.end_year as 统计末期年份,
    (e.end_spending - s.start_spending) / s.start_spending * 100 as growth
FROM start s
INNER JOIN end e ON s.country_id = e.country_id
INNER JOIN country c ON c.id = s.country_id
ORDER BY growth DESC

如果使用不支持窗口函数的数据库（如MySQL 5.7及更早版本），可用关联子查询实现相同逻辑：

-- 仅替换end CTE部分即可
end as (
SELECT
    m.country_id,
    m.year as end_year,
    m.military_spending as end_spending
FROM military m
INNER JOIN (
    SELECT country_id, MAX(year) as max_year
    FROM military
    WHERE year <= 2020 AND military_spending > 0
    GROUP BY country_id
) t ON m.country_id = t.country_id AND m.year = t.max_year
WHERE m.military_spending > 0
)

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