SQL to return list of years since a specific year

问题:

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

回答1:

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;

回答2:

Updated to return current year plus previous 5 years. Should be very fast as this is a small recordset.

SELECT YEAR(GETDATE()) as YearNum
UNION
SELECT YEAR(GETDATE()) - 1 as YearNum
UNION
SELECT YEAR(GETDATE()) - 2 as YearNum
UNION
SELECT YEAR(GETDATE()) - 3 as YearNum
UNION
SELECT YEAR(GETDATE()) - 4 as YearNum
UNION
SELECT YEAR(GETDATE()) - 5 as YearNum
ORDER BY YearNum DESC

回答3:

Using ROW_NUMBER on any column from any large enough (stable) table would be one way to do it.

SELECT *
FROM (
  SELECT TOP 100 2003 + ROW_NUMBER() OVER (ORDER BY <AnyColumn>) AS Yr
  FROM dbo.<AnyTable>
  ) Years
WHERE Yr <= YEAR(GETDATE())

Note that <AnyTable> should contain at least the amount of rows equal to the amount of years you require.

Edit (Cudo's to Joshua)

• Preferably, you'd select a table wich you know will not get truncated and/or deleted. A large enough system table should come to mind.
• At present, being a lot older and wiser (older at least), I would implement this requirement using a CTE like mentioned in the answer given by Joshua. The CTE technique is far superior and less error prone than current given ROW_NUMBER solution.

回答4:

DECLARE @YEARS TABLE (Y INT)
DECLARE @I INT, @NY INT
SELECT @I = 2004, @NY = YEAR(GETDATE())
WHILE @I <= @NY BEGIN
    INSERT @YEARS SELECT @I
    SET @I = @I + 1
END
SELECT Y 
FROM @YEARS
ORDER BY Y DESC

回答5:

Try this:

    declare @lowyear int
set @lowyear = 2004
declare @thisyear int
set @thisyear = year(getdate())
while @thisyear >= @lowyear
begin
print @thisyear
set @thisyear = (@thisyear - 1)
end

Returns

2009
2008
2007
2006
2005
2004

When you hit Jan 1, 2010. The same code will return:

2010
2009
2008
2007
2006
2005
2004

回答6:

WITH n(n) AS
(
    SELECT 0
    UNION ALL
    SELECT n+1 FROM n WHERE n < 10
)
SELECT year(DATEADD( YY, -n, GetDate())) 
FROM n ORDER BY n

回答7:

SET NOCOUNT ON
DECLARE @max int
set @max = DATEPART(year, getdate())

CREATE TABLE #temp (val int)

while @max >= 2004
  BEGIN
    insert #temp(val) values(@max)
    set @max = @max - 1
  END

SELECT * from #temp

回答8:

This is a simple query, check this

(SELECT REPLACE((TO_CHAR(SYSDATE,'YYYY')-Rownum)+1,' ',NULL) yr FROM dual CONNECT BY LEVEL < 32) year

回答9:

I think you need to create a dates table, then just select your range from it. It can also come in useful when you need to select a date range with X data attached and not have any missed days.