SQLite 3：实现列值求和并获取对应最后年份的列值

数据表与需求 #

给定names表数据：

Name      Value     Year
------------------------
John      5         2021
John      10        2022
John      6         2023
Kat       20        2021
Kat       10        2022
Kat       12        2023  

需要输出每个Name的Value总和，以及对应指定年份（示例为2023，将替换为变量）的Value值，结果如下：

Name      Total     LastYear
----------------------------
Kat       42        12
John      21        6

现有代码与问题 #

已实现前两列的SQL：

select name, sum(value) as Total from names group by name order by sum(value) desc

需要补充获取指定年份Value的LastYear列。

解决方案 #

方法1：条件聚合（通用适配多数SQL方言）

通过CASE WHEN筛选目标年份的Value，结合聚合函数提取对应值：

SELECT 
    name,
    SUM(value) AS Total,
    MAX(CASE WHEN Year = :target_year THEN Value END) AS LastYear
FROM names
GROUP BY name
ORDER BY Total DESC;

• 说明：MAX用于提取目标年份的Value，因为每个Name对应目标年份仅一条记录，使用SUM/AVG也可得到相同结果；:target_year替换为实际变量，不同数据库变量写法有差异（如MySQL用@target_year，SQL Server用@target_year，PostgreSQL用$1）。

方法2：子查询关联

先统计总和，再关联目标年份的记录获取对应值：

SELECT 
    main.name,
    main.Total,
    last_year.Value AS LastYear
FROM (
    SELECT name, SUM(value) AS Total 
    FROM names 
    GROUP BY name
) main
LEFT JOIN (
    SELECT name, Value 
    FROM names 
    WHERE Year = :target_year
) last_year ON main.name = last_year.name
ORDER BY main.Total DESC;

• 说明：使用LEFT JOIN可保证即使某个Name在目标年份无数据，仍能保留其总和记录，此时LastYear为NULL；若确认所有Name都有目标年份数据，可替换为INNER JOIN。

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