問題描述

有沒有辦法在 SQL 中完成這樣的事情:

Is there a way to accomplish something like this in SQL:

DECLARE @iter = 1

WHILE @iter<11
BEGIN
DECLARE @('newdate'+@iter) DATE = [some expression that generates a value]
SET @iter = @iter + 1
END

最后我會有 10 個變量:

At the end I would have 10 variables:

@newdate1
@newdate2
@newdate3
@newdate4
@newdate5
@newdate6
@newdate7
@newdate8
@newdate9
@newdate10

更新:

根據評論，我想我應該說明為什么我要這樣做.我正在使用 Report Builder 3.0.我將制作一份報告，其中輸入將是 start date 和 end date(除了一個其他參數).這將生成日期范圍之間的數據.但是，用戶還希望檢查集合 2013 -> 當前年份中所有其他年份的相同日期范圍.

Based on a comment, I think I should specify why I want to do this. I am working with Report Builder 3.0. I am going to make a report where the input will be a start date and an end date (in addition to one other parameter). This will generate data between the date range. However, the user also wants to check the same date range for all other years in the set 2013 -> current year.

棘手的部分是:用戶可以在 2013 年和當前年份之間的 任何 年中輸入日期范圍，我需要返回輸入年份的數據以及其他年份的數據.例如，如果用戶輸入 1/1/2014 - 6/1/2014，那么我需要返回相同的范圍，但對于 2013、2015 和 2016 年.

The tricky part is this: the user can enter a date range in any year between 2013 and the current year and I need to return data for the input year and also data for the other years. For example, if the user enters in 1/1/2014 - 6/1/2014 then I need to return the same range but for the years 2013, 2015, and 2016.

示例輸入:

1/1/2016 - 6/1/2016

報告必須為這些值生成數據:

Report must generate data for these values:

1/1/2013 - 6/1/2013
1/1/2014 - 6/1/2014
1/1/2015 - 6/1/2015
1/1/2016 - 6/1/2016

如果有更好的方法可以做到這一點，我會全力以赴.

If there is a better way to do this, I'm all ears.

推薦答案

我使用 UDF 來創建動態日期范圍.

I use a UDF to create Dynamic Date Ranges.

例如

Select DateR1=RetVal,DateR2=DateAdd(MM,5,RetVal) from [dbo].[udf-Create-Range-Date]('2013-01-01','2016-01-01','YY',1) 

退貨

DateR1      DateR2
2013-01-01  2013-06-01
2014-01-01  2014-06-01
2015-01-01  2015-06-01
2016-01-01  2016-06-01

UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1) 

精簡版 - 非 UDF這可以注入到您的 SQL 中

Stripped Down version - NON UDF This can be injected into your SQL

Declare @startdate Date ='1/1/2014'   -- user supplied value
Declare @enddate Date = '6/1/2014'    -- user supplied value

Declare @DateFrom Date = cast('2013-'+cast(month(@StartDate) as varchar(10))+'-'+cast(Day(@StartDate) as varchar(10)) as date)
Declare @DateTo Date   = cast(cast(Year(GetDate()) as varchar(10))+'-'+cast(month(@enddate) as varchar(10))+'-'+cast(Day(@enddate) as varchar(10)) as date) 
Declare @Incr int = DateDiff(MM,@startdate,@enddate)  -- made to be dynamic based on the user supplied dates

Declare @DateRange Table (DateR1 date,DateR2 Date)

;with DateTable As (
    Select DateFrom = @DateFrom
    Union All
    Select DateAdd(YY, 1, df.dateFrom)
    From DateTable DF
    Where DF.DateFrom < @DateTo
)
Insert into @DateRange(DateR1,DateR2) Select DateR1=DateFrom,DateR2=DateAdd(MM,@Incr,DateFrom) From DateTable option (maxrecursion 32767)

Select * from @DateRange

這篇關于SQL:有沒有辦法迭代聲明一個變量?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！