Sqlserver查询时间序列的函数用于左关联
/* 生成一个日期范围,如2014.01、2014.02... @Increment 步进值 @StartDate 开始日期 @EndDate 结束日期 ---------------------- @SelectedRange 返回值名称(多语句表值函数的返回值必须存放在返回值变量中) 返回表的字段和类型: IDATE DATETIME, IYEAR char(4), IMONTH char(2), IYM CHAR(7) */ ALTER FUNCTION [U_POWER].[P_DateRange] ( @Increment CHAR (1) = 'm', @StartDate DATE = '', @EndDate DATE = NULL ) RETURNS @SelectedRange TABLE ( IDATE DATE, IYM CHAR (7), IYEAR CHAR (4), IMONTH CHAR (2), IDAY char(2) ) AS BEGIN /*Default value for stored procedures parameter have to be constants. The function GETDATE() returns a different value from time to time, so it is not a constant expression. */ --IF @EndDate is null --SET @EndDate = getdate() --or SET @EndDate = COALESCE (@EndDate, GetDate()) ; WITH cteRange (P_DateRange) AS ( SELECT CAST (@StartDate AS DATE) UNION ALL SELECT CASE WHEN @Increment = 'd' THEN DATEADD(dd, 1, P_DateRange) WHEN @Increment = 'w' THEN DATEADD(ww, 1, P_DateRange) WHEN @Increment = 'm' THEN DATEADD(mm, 1, P_DateRange) END FROM cteRange WHERE P_DateRange <= CASE WHEN @Increment = 'd' THEN DATEADD(dd, - 1, @EndDate) WHEN @Increment = 'w' THEN DATEADD(ww, - 1, @EndDate) WHEN @Increment = 'm' THEN DATEADD(mm, - 1, @EndDate) END ) INSERT INTO @SelectedRange (IDATE, IYM,IYEAR, IMONTH, IDAY) SELECT P_DateRange, CONVERT (VARCHAR(7), P_DateRange, 120) [YM], YEAR (P_DateRange) [Year], MONTH (P_DateRange) [Month], DAY (P_DateRange) [Day] FROM cteRange OPTION (MAXRECURSION 0) ; RETURN END
效果如下:
SELECT * FROM U_POWER.p_DateRange ( 'm', '2015-01-01', '2017-11-16' )
请您注意
·自觉遵守:爱国、守法、自律、真实、文明的原则
·尊重网上道德,遵守《全国人大常委会关于维护互联网安全的决定》及中华人民共和国其他各项有关法律法规
·严禁发表危害国家安全,破坏民族团结、国家宗教政策和社会稳定,含侮辱、诽谤、教唆、淫秽等内容的作品
·承担一切因您的行为而直接或间接导致的民事或刑事法律责任
·您在NoteShare上发表的作品,NoteShare有权在网站内保留、转载、引用或者删除
·参与本评论即表明您已经阅读并接受上述条款