YEAR(@STARTDATE) -- 2011
YEAR(@ENDDATE) -- 2015
我该如何创建一个名为#YEARRANGE
的临时表,其中包含提供的两个日期之间的所有年份,包括@STARTDATE
和@ENDDATE
。换句话说,该表应如下所示:
#YEARRANGE
==========
- 2011
- 2012
- 2013
- 2014
- 2015
YEAR(@STARTDATE) -- 2011
YEAR(@ENDDATE) -- 2015
我该如何创建一个名为#YEARRANGE
的临时表,其中包含提供的两个日期之间的所有年份,包括@STARTDATE
和@ENDDATE
。换句话说,该表应如下所示:
#YEARRANGE
==========
- 2011
- 2012
- 2013
- 2014
- 2015
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
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
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
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
这里是另一种使用计数表的解决方案:
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)
您可以使用递归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
哎呀,可能有人比我更早就做出了这个变化。
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