blocks|key|4259603|text|如果我理解你的意图，我认为你不需要光标来做这个(你关于concat的评论)。这是我的一个例子，它获取联系人的所有电话号码，并将它们放入一个字段中，然后返回。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4259604|DECLARE+@numbers+VARCHAR(255)

SELECT+@numbers+=+COALESCE(@numbers+%2B+'+%7C+','')+%2B+PHONE_NUMB+FROM+my_table+(NOLOCK)
WHERE+CONTACT_ID=@contact_id+++RETURN+@numbers|code-block|syntax|javascript|4259605|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I don't think you need a cursor for that (your comment about concat) if I understand what you're going for.
Here's one of mine that grabs all the phone numbers for a contact and plops them in a field and returns it.

<pre><code>DECLARE @numbers VARCHAR(255)

SELECT @numbers = COALESCE(@numbers + ' | ','') + PHONE_NUMB FROM my_table (NOLOCK)
WHERE CONTACT_ID=@contact_id RETURN @numbers
</code></pre>

blocks|key|83404|text|在查询中“循环”的最快方法就是不去做。在SQL中，您应该考虑基于集合而不是基于循环。您可能应该评估您的查询，询问为什么需要循环，并寻找作为集合进行循环的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|83405|也就是说，在游标上使用FAST_FORWARD选项将有助于加快速度。|83406|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The fastest way to "loop" thru a query is to just not do it. In SQL, you should be thinking set-based instead of loop-based. You should probably evaluate your query, ask why you need to loop, and look for ways to do it as a set.

With that said, using the FAST_FORWARD option on your cursors will help speed things along.

blocks|key|83465|text|对于你声明的目标，像这样的东西实际上是一个更好的选择--完全避免“循环”问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|83466|declare+@table+table
(
++++ID+int
)

insert+into+@table+select+1+union+select+2+union+select+3+union+select+4+union+select+5

declare+@concat+varchar(256)

--+Add+comma+if+it+is+not+the+first+item+in+the+list
select+@concat+=+isnull(@concat+%2B+',+',+'')+%2B+ltrim(rtrim(str(ID)))+from+@table+order+by+ID+desc

--+or+do+whatever+you+want+with+the+concatenated+value+now...
print+@concat|code-block|syntax|javascript|83467|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For your stated goal, something like this is actually a better bet - avoids the "looping" issue entirely.

<pre><code>declare @table table
(
 ID int
)

insert into @table select 1 union select 2 union select 3 union select 4 union select 5

declare @concat varchar(256)

-- Add comma if it is not the first item in the list
select @concat = isnull(@concat + ', ', '') + ltrim(rtrim(str(ID))) from @table order by ID desc

-- or do whatever you want with the concatenated value now...
print @concat
</code></pre>

blocks|key|83343|text|这取决于你想要做什么。有些任务更适合游标，有些则适合临时表。这就是为什么它们都存在的原因。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|83344|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Depends on what you're trying to do. Some tasks are better suited for cursors, some for temp tables. That's why they both exist.

blocks|key|1041874|text|游标通常会占用大量资源，尤其是当您的表变大时。因此，如果您的表很小，我建议您使用游标，但是，较大的表可能更适合使用外部表或临时表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1041875|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Cursors are usually resource hogs especially as your table size grows. So if your table size is small I would be okay with recommending a cursor, however, a larger table would probably do better with an external or temporary table.

blocks|key|4259408|text|您是否希望遍历存储过程中或C#代码中的查询输出？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4259409|一般来说，您应该避免一次一行地遍历查询输出。SQL是针对基于集合的操作的，所以看看您是否可以使用基于集合的方法来解决您的问题。|4259410|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Do you want to loop through query output inside stored procedure OR from C# code?

Generally speaking, you should avoid looping through query output one row at a time. SQL is meant for set based operations so see if you can solve your problem using set based approach.

blocks|key|1386200|text|根据结果集的大小，表格变量在内存中，不需要磁盘读取，可以像表格一样处理(集合操作)，并且非常快，直到结果集达到内存大小(然后需要交换文件写入)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1386201|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Depending on the size of your result set - Table variables are in memory and require no disk read, can be treated just like a table (set operations) and are very fast until result set gets to large for memory (which then requires swap file writes).

blocks|key|1042059|text|以下是从返回多行的查询中获取单个字段的逗号分隔字符串的快捷方式。与游标等替代方案相比，它非常快，并且它可以是子查询的一部分(即，获取一些东西，在一列中，与其他表中的每个东西相关的所有东西的ids+)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1042060|SELECT
++++COALESCE(
++++++REPLACE(
++++++++REPLACE(
++++++++++REPLACE(
++++++++(SELECT+MyField+AS+'c'+FROM+[mytable]+FOR+XML+PATH('')),'</c><c>',','),+
++++++++++'<c>',''),
++++++++'</c>',''),
++++++'')
+++AS+MyFieldCSV|code-block|syntax|javascript|1042061|注意:如果您的列包含FOR+XML+PATH将转义的字符，则效果不佳。|1042062|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here's a shortcut to get a comma-delimited string of a single field from a query that returns a number of rows. Pretty quick compared to the alternatives of cursors, etc., and it can be part of a subquery (i.e., get some things, and in one column, the ids of all the things related to each thing in some other table):

<pre><code>SELECT
 COALESCE(
 REPLACE(
 REPLACE(
 REPLACE(
 (SELECT MyField AS 'c' FROM [mytable] FOR XML PATH('')),'&lt;/c&gt;&lt;c&gt;',','), 
 '&lt;c&gt;',''),
 '&lt;/c&gt;',''),
 '')
 AS MyFieldCSV
</code></pre>

Caveat: it won't play nice if your column contains characters that FOR XML PATH will escape.

blocks|key|1386300|text|游标不好避免游标，使用while循环代替游标，临时表中添加了键是使用循环的最好方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1386301|由于复杂的逻辑，我必须操作表中超过1000000行的行，并且游标需要2分钟。但是在将游标转换为while循环时，只需要25秒。所以在性能上有很大的不同。|1386302|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Cursor is not good avoid cursor and use while loop in place of cursor
Temp table with key added is the best way to use looping.

i have to manipulate more than 1000000 rows in the table and
for cursor take 2 min because of complex logic.
but when convert cursor in to while loop it will take 
25 seconds only. so that's big diffrence in performace.

What is the fastest way to loop thru a Query in T-SQL .
1) Cursors or 
2) Temp tables with Key added or 
any thing else.

Fastest way to loop thru a SQL Query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在T-SQL中循环查询的最快方法是什么？1)游标或2)添加了键的临时表或任何其他内容。

问遍历SQL查询的最快方法
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问遍历SQL查询的最快方法EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问遍历SQL查询的最快方法
EN