如何生成2级循环

Question

如果我有两个变量

• A=2
• B=3

我想要产生这样的结果

A  |  B  |  Text

1  |  1  |  Text1
1  |  2  |  Text2
1  |  3  |  Text3
2  |  1  |  Text4
2  |  2  |  Text5
2  |  3  |  Text6

我尝试谷歌，并可以通过这个查询达到1级。

    declare @start int = 1
    declare @end  int = 3

    ;with numcte  
    AS  
    (  
      SELECT @start as [SEQUENCE]
      UNION all  
      SELECT [SEQUENCE] + 1
      FROM numcte WHERE [SEQUENCE] < @end 
    )      
    SELECT  [SEQUENCE], 'text' + CAST([SEQUENCE] as varchar) as [text] FROM numcte

如何实现2级循环？

Answer 1

一个相当简单的方法是：

select a.a, b.b, concat('text', row_number() over (order by a, b))
from (values (1), (2)) a(a) cross join
     (values (1), (2), (3)) b(b);

或者如果您真的想声明变量：

declare @a int = 2;
declare @b int = 3;
with n as (
      select 1 as n union all
      select n + 1
      from n
      where n < @a or n < @b
    )
select na.n as a, nb.n as b, concat('text', row_number() over (order by na.n, nb.n))
from n na join
     n nb
     on na.n <= @a and nb.n <= @b;

这里是db<>fiddle。

Answer 2

使用数字表(许多示例可以通过搜索使用)。产生一个可能非常简化的例子的方法之一是：

with cte as (
   select 1 as num 
   union all select num + 1 from cte where num < 3 )
select cte.num, cte2.num from cte  
cross join cte as cte2 
where cte.num in (1, 2)
order by cte.num, cte2.num
;

努力解决这个问题--它看起来可能令人望而生畏。开始从集合的角度思考！小提琴