使用Declare value作为表

Question

我目前正在使用以下命令创建一个动态的表

DECLARE @startnum INT=1000
DECLARE @endnum INT=1004
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT * FROM gen
option (maxrecursion 0)

我想知道是否有一种方法可以在子查询中使用这个表进行连接。例如，我如何用上表替换下面代码中的a2。

select a1.*, a2.*
from
(select 
      1000 as v1
union all
select
      1001 as v1
union all
select
      1003 as v1)a1
left join
(select 
      1000 as v2
union all
select
      1001 as v2
union all
select
      1003 as v2
union all 
select
      1004 as v2)a2
    ON a1.v1=a2.v2

提前谢谢你。

Answer 1

尝试如下所示，只需添加另一个cte并使用join

DECLARE @startnum INT=1000
DECLARE @endnum INT=1004
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
),cte as
(
SELECT * FROM gen

) , cte1 as
(
select a1.* from
(select 
      1000 as v1
union all
select
      1001 as v1
union all
select
      1003 as v1
      )a1

) select cte.*,cte1.* from cte join cte1 on cte.num=cte1.v1

Answer 2

使用CTE：

WITH gen AS (
      SELECT @startnum AS num
      UNION ALL
      SELECT num + 1
      FROM gen
      WHERE num + 1 <= @endnum
     )
SELECT
FROM gen JOIN
     a1
     ON a1.v1 = gen.num
OPTION (MAXRECURSION 0);

唯一真正的诀窍是，OPTION需要位于整个查询的末尾。