最近5年得sql查询与分组依据？

Question

下面是我到目前为止所获得的查询，但是，我只需要得到最近5年的数据，这就得到了所有年份的数据。我尝试为sql语句添加top5，它似乎不起作用，它显示了一些错误。如何修改此查询以只获取最近5年的数据？

select (sum(X) + sum(Y)) as tot,some_date
from table where fkid = 1 
group by year order by year desc;

下面是我所期望的数据格式。

年度合计
552,074.78 2012-01-01 466,283.62 2011-01-01 640,813.37 2010-01-01 721,182.28 2009-01-01 719,676.05 2008-01-01

Answer 1

您的查询的问题很容易（假设这是唯一的问题）。year是保留字，因此用双引号环绕：

select (sum(X) + sum(Y)) as tot, "year"
from table
where fkid = 1 
group by "year"
order by "year" desc;

得到最后五年是另一回事。基于年列，您可以执行以下操作：

select (sum(X) + sum(Y)) as tot, "year"
from table
where fkid = 1 and year >= year(current timestamp) - 5
group by "year"
order by "year" desc;

或者，您实际上可能有另一列，该列有一个日期时间，您可以使用它来代替“year”。

select (sum(X) + sum(Y)) as tot, year(dt) as "year"
from table
where fkid = 1 and dt >= CURRENT DATE - 5 years and dt <= CURRENT DATE
group by year(dt)
order by year(dt) desc;