SQL - 遍历表记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2207141/
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
SQL - Iterating through table records
提问by user255048
I have created user-defined function that converts a comma-delimited string into a table. I execute this function like so:
我创建了用户定义的函数，可以将逗号分隔的字符串转换为表格。我像这样执行这个函数：
select [String] as 'ID' from dbo.ConvertStringToTable('1,2,3,4')
The results from this query look like the following:
此查询的结果如下所示：
ID
--
1
2
3
4
In reality, I want to iterate through each of the rows in this table. However, I cannot figure out how to do this. Can someone show me some sample SQL of how to iterate through the rows of the table?
实际上，我想遍历该表中的每一行。但是，我无法弄清楚如何做到这一点。有人可以向我展示一些有关如何遍历表行的示例 SQL 吗？
回答by Kane
In SQL SERVER 2000/05/08 you can use a Cursor as shown below.
在 SQL SERVER 2000/05/08 中，您可以使用如下所示的 Cursor。
However before you go down the cursor path you should first look into the problems associated with cursors in SQL Server.
但是，在沿着游标路径前进之前，您应该首先查看与 SQL Server 中的游标相关的问题。
DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [String] AS 'ID'
FROM [dbo].[ConvertStringToTable]('1,2,3,4')
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @id
-- do your tasks here
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
回答by HLGEM
Don't use the cursor if you can avoid it, normally all you really need is to join to the table that you created. If your cursor is doing an update, insert, or delete, you have a 99.9% chance of not needing a cursor. Cursors should be a technique of LAST resort not first resort. Iterating through records is almost always a poor choice in a database. Learn to think in sets.
如果可以避免，请不要使用游标，通常您真正需要的只是连接到您创建的表。如果您的游标正在执行更新、插入或删除操作，您有 99.9% 的机会不需要游标。游标应该是最后的手段而不是第一手段。在数据库中遍历记录几乎总是一个糟糕的选择。学习成套思考。
Why should you avoid cursors? Becasue they create performance nightmares. I've changed processes from taking 24 hours or more to less than a minute by removing the curosr.
为什么要避免使用游标？因为它们会造成性能噩梦。通过删除curosr，我已将流程从需要24 小时或更长时间更改为不到一分钟。
回答by Atithi
Use the below function , which takes the string and the delimiter symbol....
使用下面的函数，它接受字符串和分隔符....
CREATE FUNCTION [dbo].[Udfsplitstring](@Text VARCHAR(MAX),
@Delimiter VARCHAR(20) = ' ')
-- @Strings table will contain values after separated by delimiter
RETURNS @Strings TABLE (
ID INT IDENTITY PRIMARY KEY,
VALUE VARCHAR(MAX))
AS
BEGIN
DECLARE @Index INT
-- Set the index to -1 prior to run index through the loop
SET @Index = -1
-- Run loop till Text becomes empty
WHILE ( Len(@Text) > 0 )
BEGIN
-- Getting the index of first delimiter
SET @Index = Charindex(@Delimiter, @Text)
-- Checking if there is no delimiter in Text
IF ( @Index = 0 )
AND ( Len(@Text) > 0 )
BEGIN
-- Inserting text which separated by delimiter
INSERT INTO @Strings
VALUES (Rtrim(Ltrim(@Text)))
BREAK
END
-- Checking if index found in Text then run the following script
IF ( @Index > 1 )
BEGIN
-- Inserting text after separated by delimiter
INSERT INTO @Strings
VALUES (LEFT(@Text, @Index - 1))
-- Separate the inserted value from text
SET @Text = Rtrim(Ltrim(RIGHT(@Text, ( Len(@Text) - @Index )))
)
END
ELSE
-- Separate the inserted value from text
SET @Text = Rtrim(Ltrim(RIGHT(@Text, ( Len(@Text) - @Index ))))
END
RETURN
END
回答by Arthur
The answer would be cursors, but if there is any chance not to use cursor I would suggest to use the other solution.
答案是游标，但如果有机会不使用游标，我建议使用其他解决方案。
Your Query looks like an SQL Server Query, so here is the Documentation for SQL Server 2008.
您的查询看起来像 SQL Server 查询，因此这里是 SQL Server 2008 的文档。
