SQL 返回自特定年份以来的年份列表 [英] SQL to return list of years since a specific year

问题描述

我需要一个年份列表作为记录集，从 2004 年到当年(按降序排列)，无需编写存储过程.这可能吗?(SQL Server 2005).所以它应该返回:

I need a list of years as a recordset starting with 2004 to current year (in desc order), without writing a stored procedure. Is this possible? (SQL Server 2005). So it should return:

2009
2008
2007
2006
2005
2004

2009
2008
2007
2006
2005
2004

推荐答案

使用递归 CTE 获取从 2004 年到现在的所有年份:

This gets all years from 2004 to the present, using a recursive CTE:

with yearlist as 
(
    select 2004 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)

select year from yearlist order by year desc;

这篇关于SQL 返回自特定年份以来的年份列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！