迭代T-SQL中的字符列表

I am looking for any efficient way to solve the following problem in T-SQL:

我正在寻找任何有效的方法来解决T-SQL中的以下问题:

for i in L = ['A', 'B',..., 'N'] 
INSERT INTO MyTable1 (SomeCol1, SomeCol2)
SELECT SomeCol1, SomeCol2 FROM SomeTable1 WHERE <conditions>
UPDATE MyTable1 SET MyCol1 = i WHERE MyCol1 = 'the default value of MyCol1'

Given that my list of characters L is long, I don't want to do this once for each element separately. So, if there is a better solution than using a loop, I am interested in that as well as long as it solves the issue. Please note that I am not trying to use correct syntax above - I would rather also like to know how to approach the part for i in L = ['A', 'B',..., 'N'] in T-SQL.

鉴于我的字符列表L很长,我不想单独为每个元素执行一次。因此,如果有一个比使用循环更好的解决方案,我对此感兴趣,只要它解决了问题。请注意,我并没有尝试使用上面的正确语法 - 我还想知道如何在T中接近L = ['A','B',...,'N']的部分。 SQL。

Thanks!

2 个解决方案

#1

Your question is very unclear...

你的问题很不清楚......

SQL-Server is a set-based tool. Whenever you feel the need to loop / iterate over a set you are assumably approaching your issue from the wrong side.

SQL-Server是一个基于集合的工具。每当你觉得需要循环/迭代一套时,你可以从错误的方面接近你的问题。

First question: How are you providing your list of characters? Is it a table, JSON, XML, a CSV-String?

第一个问题:你如何提供你的角色列表?它是一个表,JSON,XML,一个CSV字符串?

In any case your goal must be to get it as a set.

无论如何,你的目标必须是将其作为一套。

Just to show the principles: If you provide your characters e.g. as a simple list you can use a recursive CTE

只是为了说明原则:如果你提供你的角色,例如作为一个简单的列表,您可以使用递归CTE

DECLARE @L VARCHAR(100)='ACFHJTZJ'; --add as many as you need

WITH recCTE AS
(
    SELECT 1 AS Pos,SUBSTRING(@L,1,1) AS OneChar
    UNION ALL
    SELECT r.Pos+1, SUBSTRING(@L,r.Pos+1,1)
    FROM recCTE r
    WHERE r.Pos<LEN(@L)
)
SELECT /*add DISTINCT if you want to surpress repeating characters*/ 
       Pos,OneChar 
FROM recCTE;

This will return all letters as a derived table.

这将返回所有字母作为派生表。

You can use such a set (however you create it) in a set-based statement like here:

您可以在基于集合的语句中使用这样的集合(但是您创建它),如下所示:

DECLARE @L VARCHAR(100)='ACFHJTZJ'; --add as many as you need
DECLARE @tbl TABLE(SomeColumn CHAR(1));

WITH recCTE AS
(
    SELECT 1 AS Pos,SUBSTRING(@L,1,1) AS OneChar
    UNION ALL
    SELECT r.Pos+1, SUBSTRING(@L,r.Pos+1,1)
    FROM recCTE r
    WHERE r.Pos<LEN(@L)
)
INSERT INTO @tbl(SomeColumn)
SELECT DISTINCT OneChar 
FROM recCTE;

SELECT * FROM @tbl;

This would insert each separate char into the table @tbl. Of course you can use this in any other context (like a WHERE condition) too.

这会将每个单独的char插入到表@tbl中。当然,您也可以在任何其他上下文(如WHERE条件)中使用它。

In any case, this statement would not have to change whether your list includes 1, 10 or a thousand of elements.

在任何情况下,此声明都不必更改您的列表是否包含1个,10个或数千个元素。

If you need more help, you really should provide more background information. Best was to create a stand-alone scenario as I've done above. And you might want to read about the XY-Problem...

如果您需要更多帮助,您应该提供更多背景信息。最好是创建一个独立的场景,就像我上面所做的那样。你可能想读一下XY问题......

#2

This solved my issue and I guess it's far from the optimal way to do it.

这解决了我的问题,我想这远不是最佳方式。

CREATE TABLE MyTable2 (MyCol2 VARCHAR(5))
INSERT INTO MyTable2 (MyCol2) VALUES ('A')
INSERT INTO MyTable2 (MyCol2) VALUES ('B')
.
.
.
INSERT INTO MyTable2 (MyCol2) VALUES ('N')
ALTER TABLE MyTable2 ADD ID INT IDENTITY

WHILE (SELECT COUNT(*) FROM MyTable2) > 0
BEGIN
  INSERT INTO MyTable1 (SomeCol1, SomeCol2)
  SELECT SomeCol1, SomeCol2 FROM SomeTable1 WHERE <conditions>
  UPDATE MyTable1 
  SET MyCol1 = (SELECT MyCol2 FROM MyTable2 HAVING ID = MIN(ID)) 
  WHERE MyCol1 = 'the default value of MyCol1'
  DELETE FROM MyTable2 WHERE MyCol2 = (SELECT MyCol2 FROM MyTable2 HAVING ID = MIN(ID))
END

#1