sql – 用cte创建while循环

发布时间：2020-12-12 06:43:02 所属栏目：MsSql教程来源：网络整理

导读：如何从while循环创建sql server cte 我这样的循环 declare @ind as int declare @code as nvarchar set @ind = 0 while @ind 884 begin select @ind = @ind + 1 --here execute Procedure --and set return value to variable set @code = cast (@ind as nvar

如何从while循环创建sql server cte
我这样的循环

declare @ind as int
  declare @code as nvarchar
  set @ind  = 0
   while @ind < 884
  begin
    select @ind  = @ind  + 1
    --here execute Procedure 
        --and set return value to variable 
    set @code = cast (@ind   as nvarchar)
  end

解决方法

如果你需要表：

;WITH Sec(Number) AS 
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sec
    WHERE Number < 884
) 

SELECT * FROM Sec
OPTION(MAXRECURSION 0)

如果你需要一个字符串：

;WITH Sec(Number) AS 
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sec
    WHERE Number < 884
) 

SELECT STUFF(a.[Str],1,'')
FROM
(
    SELECT (SELECT ',' + CAST(Number AS NVARCHAR(3)) 
    FROM Sec
    FOR XML PATH(''),TYPE
    ).value('.','varchar(max)') AS [Str] 
) AS a
OPTION(MAXRECURSION 0)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!