遍历字符串列表在SQL Server中？

一尘不染

遍历字符串列表在SQL Server中？

sql

如何在T-SQL的列表中访问值？

我有一条SQL语句，该语句循环遍历并计算值在中的特定列中出现的次数Table_1。然后，将所需的值插入其上的列，Table_2或者如果该行不存在，则添加新行并添加必要的数据。

我创建了确切的列表或表格，

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 @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

我要实现的目标是遍历包含8个项目的列表，然后将这些值输入其对应的列中。

例如，

在这一行上，我已经硬编码Data1，

WHERE Staff_No = 3201 AND Date_Data 
       BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data1'

我想做的就是这个

WHERE Staff_No = 3201 AND Date_Data 
       BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = @MyList[@cnt]

还有这个，

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]

但这是行不通的。经过更多研究之后，我发现T-
SQL实际上并没有创建一个list，而是一个临时表，可以这么说，您需要从那里获取价值。不幸的是，我似乎什么也无法工作。

我有一个UPDATE和INSERT声明，我需要从列表中添加值。

编辑：最后一刻的代码调整，

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

阅读 145

2021-03-17

共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')

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

印刷：

Data1
Data2
Data3
Data4
Data5
Data6
Data7
Data8

所以你里面有@value变量Data，Data2..等等。我认为这可以解决您的问题。

另一种方法是使用`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

您得到相同的结果

2021-03-17