获取两个日期之间的所有年份

Question

如果我有两年经验，例如：

YEAR(@STARTDATE) -- 2011
YEAR(@ENDDATE)   -- 2015

我该如何创建一个名为#YEARRANGE的临时表，其中包含提供的两个日期之间的所有年份，包括@STARTDATE和@ENDDATE。换句话说，该表应如下所示：

#YEARRANGE
==========
- 2011
- 2012
- 2013
- 2014
- 2015

- user818700

---

可能是重复的问题：https://dev59.com/qHRB5IYBdhLWcg3wbGtB意思：这可能是一个重复的问题，链接指向了一个类似的问题。该问题涉及如何编写SQL以返回自特定年份以来的年份列表。 - Evaldas Buinauskas

Answer 1

您可以尝试这样做：

DECLARE 
      @start DATE = '20120201'
    , @end DATE = '20150405'

;WITH cte AS 
(
    SELECT YEAR(@start) AS [Year]
    UNION ALL
    SELECT yl.[Year]+ 1 as [Year]
    FROM cte  yl
    WHERE yl.[year] + 1 <= Year(@end)
)
SELECT CONVERT(CHAR(4), [Year], 120) 
FROM cte

- Rahul Tripathi

Answer 2

CREATE TABLE #YEARRANGE
(
    Years INT
)

DECLARE @STARTDATE DATE,
        @ENDDATE DATE;
SET @STARTDATE = '20110101';
SET @ENDDATE = '20150101';

WITH GetYears 
AS
(
    SELECT YEAR(@STARTDATE) AS Years
    UNION ALL
    SELECT yl.Years+ 1 as Years
    FROM GetYears yl
    WHERE yl.Years + 1 <= YEAR(@ENDDATE)
)
INSERT INTO #YEARRANGE
SELECT Years FROM GetYears ORDER BY Years;

SELECT * FROM #YEARRANGE

DROP TABLE #YEARRANGE

输出：

year
2011
2012
2013
2014
2015

- Stanislovas Kalašnikovas

Answer 3

DECLARE @STARTDATE DATE, @ENDDATE DATE

SET @STARTDATE = '2011-01-10'
SET @ENDDATE = '2015-06-16'

CREATE TABLE #YEARRANGE([YEAR] INT)

;WITH C AS (
        SELECT  DATEPART(YEAR, @STARTDATE) AS yr
        UNION ALL
        SELECT yr + 1
        FROM C
        WHERE yr < DATEPART(YEAR, @ENDDATE)
        )
INSERT INTO #YEARRANGE
SELECT yr
FROM C
ORDER BY yr

SELECT * FROM #YEARRANGE

输出

YEAR
------------
2011
2012
2013
2014
2015

- sqluser

Answer 4

DECLARE @STARTDATE INT  = 2011

DECLARE @ENDDATE INT = 2015

CREATE TABLE #YEARRANGE([YEAR] INT)

WHILE @STARTDATE <= @ENDDATE
BEGIN
    INSERT INTO #YEARRANGE
    VALUES(@STARTDATE)

    SET @STARTDATE = @STARTDATE + 1
END


SELECT * FROM #YEARRANGE

DROP TABLE #YEARRANGE

- Mukund

Answer 5

这里是另一种使用计数表的解决方案：

DECLARE @startDate DATE = '20110101'
DECLARE @endDate DATE = '20150101'

;WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP(DATEDIFF(YEAR, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
)
SELECT
    YEAR(@startDate) + (N-1)
FROM Tally
WHERE
    YEAR(@startDate) + (N-1) <= YEAR(@endDate)

- Felix Pamittan

Answer 6

您可以使用递归CTE，然后直接使用select into语句创建临时表，该语句会自动创建临时表而无需显式创建最后一个：

DECLARE @sd INT = 2011, @ed INT = 2015

;WITH cte AS(SELECT @sd AS y
             UNION ALL
             SELECT y + 1 FROM cte WHERE y < @ed)
SELECT * INTO #YEARRANGE FROM cte

- Giorgi Nakeuri

Answer 7

哎呀，可能有人比我更早就做出了这个变化。

Declare @StartYear int = 2011
    , @EndYear int = 2015
Declare @YearDiff int = @EndYear - @StartYear 
    , @i int =0 --Counter

if OBJECT_ID('tempdb..#YEARRANGE') is not null
    drop table #YEARRANGE;
Create Table #YEARRANGE (YearRange int)

-- I hate loops in SQL.
while @i <= @YearDiff
begin
    INSERT INTO #YEARRANGE
    select @i + @StartYear
    set @i +=1;
end

/*  Tada    */
Select * 
from #YEARRANGE

- Migo