你的分享就是我们的动力 ---﹥

生成1到300个数目字的方法

生成1到300个数字的方法

生成1到300个数字的方法

 

方法一

cross join

SELECT aa.[num]+bb.[num]+cc.[num] FROM 
(SELECT 0 num UNION ALL
SELECT 1 num UNION ALL
SELECT 2 num UNION ALL
SELECT 3 num UNION ALL
SELECT 4 num UNION ALL
SELECT 5 num UNION ALL
SELECT 6 num UNION ALL
SELECT 7 num UNION ALL
SELECT 8 num UNION ALL
SELECT 9 num ) aa
CROSS JOIN
(SELECT 0 num UNION ALL
SELECT 10 num UNION ALL
SELECT 20 num UNION ALL
SELECT 30 num UNION ALL
SELECT 40 num UNION ALL
SELECT 50 num UNION ALL
SELECT 60 num UNION ALL
SELECT 70 num UNION ALL
SELECT 80 num UNION ALL
SELECT 90 num ) bb
CROSS JOIN
(SELECT 0 num UNION ALL
SELECT 100num UNION ALL
SELECT 200 num  ) cc
ORDER BY 1

 

 

 

方法二

while循环

DECLARE @i INT
DECLARE @tb TABLE(a INT)
SET @i=1
    INSERT INTO @tb
            ( [a] )
    VALUES  (  @i  -- a - int
              )
WHILE (@i<300)
BEGIN
    SET @i=@i+1
    INSERT INTO @tb
            ( [a] )
    VALUES  (  @i  -- a - int
              )
END
SELECT * FROM @tb

 

方法三

CTE递归

;with cte_temp
as
(
    select 0 as id
    union all
    select id+1 from cte_temp where id<301
)
select id from cte_temp option (maxrecursion 301);

 

2楼wy123
加一种更加优雅的实现方式,;with cte_tempas(select 0 as idunion allselect id+1 from cte_temp where idlt;301)select id from cte_temp option (maxrecursion 301);
Re: 桦仔
@wy123,引用加一种更加优雅的实现方式,,递归也可以,不错
1楼Lvanhades666
还可以使用这种,跟楼上类似,WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), --2,B2 AS(SELECT n=1 FROM B1 a CROSS JOIN B1 b), --4,B3 AS(SELECT n=1 FROM B2 a CROSS JOIN B2 b), --16,B4 AS(SELECT n=1 FROM B3 a CROSS JOIN B3 b), --256,B5 AS(SELECT n=1 FROM B4 a CROSS JOIN B4 b), --65536,CTE AS(SELECT r=ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM B5 a CROSS JOIN B3 b) --65536 * 16,,剩下的就是根据自己的需求调整了