SQL 如何在sql server的while循环中使用select表达式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14604965/
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
how to use select expression in while loop in sql server?
提问by mjyazdani
I need to use a select expression in a while loop and I use the below sample code:
我需要在 while 循环中使用选择表达式，并使用以下示例代码：
declare @i integer
set @i=1
while (@i<10)
begin
select @i as m;
set @i=@i+1
END
this code returns 10 separate table! I want it to return all select results in one table... is that possible? if yes... how?
此代码返回 10 个单独的表！我希望它在一个表中返回所有选择结果......这可能吗？如果是的话……怎么样？
回答by squillman
You can use a temp table or table variable for this.
您可以为此使用临时表或表变量。
Here's how to do it using a temp table.
以下是使用临时表的方法。
CREATE TABLE #t (m INT)
DECLARE @i INT
SET @i=1
WHILE (@i<10)
BEGIN
INSERT INTO #t SELECT @i
SET @i=@i+1
END
SELECT m FROM #t
Very similar with a table variable
与表变量非常相似
DECLARE @t TABLE (m INT)
DECLARE @i INT
SET @i=1
WHILE (@i<10)
BEGIN
INSERT INTO @t SELECT @i
SET @i=@i+1
END
SELECT m FROM @t
回答by Hamlet Hakobyan
It is not possible. Each SELECTstatement generates its own result set. You can use temp table to add results of each iteration and then get all in one table. To generate sequence of integers you can use this (for SQL SERVER 2005 + )
这不可能。每个SELECT语句都会生成自己的结果集。您可以使用临时表将每次迭代的结果添加到一个表中。要生成整数序列，您可以使用它（对于 SQL SERVER 2005 +）
;WITH CTE
AS
(
SELECT 1 N
UNION ALL
SELECT N + 1 FROM CTE
WHERE N<10
)
SELECT N FROM CTE
回答by andrew
squillman got it...with #t (create table # - table persisted at top-level scope while session is open - if you have several batch statements, you can reference this table in any after declaration until you drop the table)
squillman 得到了它......使用#t（创建表# - 会话打开时表在顶级范围内持久化 - 如果您有多个批处理语句，您可以在任何 after 声明中引用该表，直到您删除该表）
Cris also got it with @test (declare @ table - variable - only persisted in the current scope - single execution batch block...note that there are several performance issues that can be introduced if you use this)
Cris 也用@test 得到了它（声明@table - 变量 - 只在当前范围内持久化 - 单执行批处理块...注意，如果你使用它，可能会引入几个性能问题）
the last type of temp table you can use is a global temp table (create table ## - lasts as long as the session that created it stays open or until it is dropped)
您可以使用的最后一种临时表类型是全局临时表（创建表 ## - 只要创建它的会话保持打开状态或直到它被删除，就会持续下去）
with #t, you may want to add this to the beginning of your script if you don't close the session:
使用#t，如果您不关闭会话，您可能希望将其添加到脚本的开头：
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
Enjoy the temp tables!
享受临时表！
回答by Cris
declare @i integer
DECLARE @test TABLE(
m /*your data type*/
)
set @i=1
while (@i<10)
begin
insert into @test select @i;
set @i=@i+1
END
select * from @test
