SQL Server：使用查询生成分隔数字列表（具有最小/最大阈值）

I did some searching here on this.. but it was either for PHP, Python or didnt relate.

我在这里做了一些搜索..但它是PHP,Python或没有关联。

I understand this is somewhat of an odd use for a query..

我知道这对查询来说有点奇怪。

I have an existing app.

我有一个现有的应用程序。

As a parameter to a section in this app (which is for name value pairs).. I can either pass in a string, or a query.

作为此应用程序中某个部分的参数(用于名称值对)..我可以传入字符串或查询。

I'd rather not try use a hardcoded NVP 'string' to build this list.. but instead use a query to do this.

我宁愿不尝试使用硬编码的NVP'字符串'来构建此列表..而是使用查询来执行此操作。

It wont be using any table or real data.. just used to build a NVP list of years actually.

它不会使用任何表格或真实数据..仅用于构建实际年份的NVP列表。

What I'm hoping to get help with is to build a string output of years from 1920-2018* (*current year..so this doesnt need to be manually updated each year)..

我希望得到帮助的是建立一个1920年至2018年的字符串输出*(*当前年份。所以这不需要每年手动更新)..

I'd also like to have/inject a unique entry at the end. Current or Present.

我还想在最后注入/注入一个唯一的条目。现在或现在。

So in the end I get an output like so:

所以最后我得到一个这样的输出:

1920|1920|1921|1921|1922|1922.........2018|2018|Present|Present

Is this possibly using a query?

这可能是使用查询吗?

2 个解决方案

#1

How's this?

declare @string varchar(max)=''
declare @iterator int =1
declare @year int=1920
while @year<=year(getdate())
begin
set @string=@string+'|'+cast(@year as varchar(4))+'|'+cast(@year as varchar(4))
set @year=@year+1
end
select @string+'|Present|Present'

#2

You could use a loop to build the string:

您可以使用循环来构建字符串:

DECLARE @YearString varchar(MAX) = '', @Year varchar(4) = 1920, @YearStringFinal varchar(MAX)

WHILE @Year <= YEAR(GETDATE())
BEGIN
SET @YearString = @YearString+@Year+'|'+@Year+'|'
SET @Year = @Year +1
END

SET @YearStringFinal = @YearString + 'Present|Present'

SELECT @YearStringFinal

I imagine you'll want to use a procedure for this though:

我想你会想要使用一个程序:

CREATE PROCEDURE GetYearString @YearString varchar(MAX) = '', @Year varchar(4) = 1920, @YearStringFinal varchar(MAX) OUTPUT
AS
BEGIN

WHILE @Year <= YEAR(GETDATE())
BEGIN
SET @YearString = @YearString+@Year+'|'+@Year+'|'
SET @Year = @Year +1
END

SET @YearStringFinal = @YearString + 'Present|Present'

END

Then output the string and use it as your parameter:

然后输出字符串并将其用作参数:

DECLARE @String varchar(MAX)
EXEC GetYearString @YearStringFinal = @String OUTPUT

SELECT @String

#1