遍历 SQL Server 中的字符串列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38388144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Iterate through a list of strings In SQL Server?
提问by KyloRen
How do you access the value within a list in T-SQL?
如何在 T-SQL 中访问列表中的值？
I have a SQL statement that loops through and counts how many times a value appears in a specific column in Table_1. Then it inserts the required values into their columns on Table_2or if the row dose not exist, it adds a new row and adds the necessary data.
我有一个 SQL 语句，它循环遍历并计算值在Table_1. 然后它将所需的值插入到它们的列中，Table_2或者如果该行不存在，它会添加一个新行并添加必要的数据。
I created the list or table to be exact,
确切地说，我创建了列表或表格，
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
The statement works fine, but I have everything hardcoded and I want to add some dynamic data to be inserted, so I created a list of values (Strings). Now I can't access the values the way I though I could.
该语句工作正常，但我对所有内容进行了硬编码，并且我想添加一些要插入的动态数据，因此我创建了一个值列表（字符串）。现在我无法以我可以的方式访问这些值。
This is the whole statement,
这是整个声明，
DECLARE @cnt INT = 1;
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
WHILE @cnt < 9
BEGIN
IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt)
BEGIN
UPDATE Staff_Manager.dbo.Staff_Count_TBL
SET Column_Value = (
SELECT COUNT(*)
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1'
GROUP BY Staff_No, Info_Data),
Column_Value2 = 'Data1'
WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt
END
ELSE
BEGIN
INSERT INTO Staff_Manager.dbo.Staff_Count_TBL
(Staff_No, Year_D, Month_D, Column_Index, Column_Value, Column_Value2)
SELECT 3201, 2016, 6, @cnt, COUNT(*), 'Data1'
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1'
GROUP BY Staff_No, Info_Data
END
SET @cnt = @cnt + 1
END
What I am trying to achieve is to loop through a list that has 8 items in it and enter those values into their corrosponding columns.
我想要实现的是循环遍历一个包含 8 个项目的列表，并将这些值输入到相应的列中。
Eg,
例如，
On this line I have hardcoded Data1,
在这一行我已经硬编码Data1，
WHERE Staff_No = 3201 AND Date_Data
BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1'
What I am trying to do is this,
我想做的是这个，
WHERE Staff_No = 3201 AND Date_Data
BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]
And this,
和这个，
SELECT 3201, 2016, 6, @cnt, COUNT(*), @MyList[@cnt]
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data
BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]
But that does not work. After researching a little more, I found that T-SQL does not actually make a list , but a temp Table so to speak and you need to get the value from there. Unfortunately I can't seem to get anything to work.
但这不起作用。经过更多的研究，我发现 T-SQL 实际上并没有创建一个列表，而是一个临时表，可以这么说，你需要从那里获取值。不幸的是，我似乎无法得到任何工作。
I have an UPDATEand a INSERTstatement that I need to add the value from the list.
我有UPDATE一个INSERT声明，我需要从列表中添加值。
EDIT: Last minute code tweaking,
编辑：最后一分钟的代码调整，
DECLARE @cnt INT = 1;
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
INSERT INTO @MyList VALUES ('Data9')
INSERT INTO @MyList VALUES ('Data10')
INSERT INTO @MyList VALUES ('Data11')
INSERT INTO @MyList VALUES ('Data12')
INSERT INTO @MyList VALUES ('Data13')
INSERT INTO @MyList VALUES ('Data14')
INSERT INTO @MyList VALUES ('Data15')
INSERT INTO @MyList VALUES ('Data16')
INSERT INTO @MyList VALUES ('Data17')
INSERT INTO @MyList VALUES ('Data18')
INSERT INTO @MyList VALUES ('Data19')
INSERT INTO @MyList VALUES ('Data20')
INSERT INTO @MyList VALUES ('Data21')
INSERT INTO @MyList VALUES ('Data22')
INSERT INTO @MyList VALUES ('Data23')
INSERT INTO @MyList VALUES ('Data24')
INSERT INTO @MyList VALUES ('Data25')
INSERT INTO @MyList VALUES ('Data26')
INSERT INTO @MyList VALUES ('Data27')
INSERT INTO @MyList VALUES ('Data28')
INSERT INTO @MyList VALUES ('Data29')
INSERT INTO @MyList VALUES ('Data30')
DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);
WHILE @cnt <= @MAX
BEGIN
SET @VALUE = (SELECT Value FROM @MyList
ORDER BY 1 OFFSET @COUNTER
ROWS FETCH NEXT 1 ROWS ONLY);
PRINT @cnt
PRINT @VALUE
PRINT @COUNTER
IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3005 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt)
BEGIN
UPDATE Staff_Manager.dbo.Staff_Count_TBL
SET Column_Value = (
SELECT COUNT(*)
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3005 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @value
GROUP BY Staff_No, Info_Data),
Column_Value2 = @value
WHERE Staff_No = 3005 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = @cnt
END
ELSE
BEGIN
INSERT INTO Staff_Manager.dbo.Staff_Count_TBL
(Staff_No, Year_D, Month_D, Column_Index, Column_Value, Column_Value2)
SELECT 3005, 2016, 6, @cnt, COUNT(*), @value
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3005 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @value
GROUP BY Staff_No, Info_Data
END
SET @cnt = @cnt + 1
SET @COUNTER = @COUNTER + 1
END
回答by Zein Makki
What you created is not a list but a table variable. So how to Iterate over a table. Below is a simple example and I think you can proceed after if you understand it:
您创建的不是列表而是表变量。那么如何迭代一个表。下面是一个简单的例子，如果你理解了，我认为你可以继续：
(Note: Cursors are not efficient when it comes to performance and large tables)
（注意：游标在性能和大表方面效率不高）
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
DECLARE @value VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT Value FROM @MyList
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @value
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @value
-- PUT YOUR LOGIC HERE
-- MAKE USE OR VARIABLE @value wich is Data1, Data2, etc...
FETCH NEXT FROM db_cursor INTO @value
END
CLOSE db_cursor
DEALLOCATE db_cursor
Prints:
印刷：
Data1
Data2
Data3
Data4
Data5
Data6
Data7
Data8
So you have inside @value variable Data, Data2.. etc . I think this solves your problem.
所以你有 @value 变量Data，Data2.. 等。我认为这可以解决您的问题。
Alternative way is using a WHILEloop + OFFSET+ FETCH NEXT:
另一种方法是使用WHILE循环OFFSET++ FETCH NEXT：
DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')
DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);
WHILE @COUNTER < @MAX
BEGIN
SET @VALUE = (SELECT VALUE FROM
(SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , Value from @MyList) R
ORDER BY R.[index] OFFSET @COUNTER
ROWS FETCH NEXT 1 ROWS ONLY);
PRINT @VALUE
SET @COUNTER = @COUNTER + 1
END
You get the same result
你得到同样的结果
