在 WITH 表达式中声明变量（SQL Server）？

Question

在WITH表达式中是否有一种定义变量的方法？像这样：

;WITH SomeName AS ( 
    declare @somevar decimal
    set @somevar = ...
    SELECT ....)

可能吗？

- grady

Answer 1

不可以，在CTE之外声明/设置它。

DECLARE @somevar DECIMAL
;WITH SomeName AS (SELECT....)
SELECT @somevar = ...
FROM SomeName

根据MSDN的说法：

CTE_query_definition指定了一个SELECT语句，其结果集用于填充公共表达式。 CTE_query_definition的SELECT语句必须满足与创建视图相同的要求，但是CTE不能定义另一个CTE。

- AdaTheDev

---

一个公共表达式应该返回行。我认为你根本不能用它来分配变量。 - Andriy M

@Andriy M - 是的，CTE 必须返回一个结果集。不过，如上所述，您可以将值分配到变量中来自 CTE，但是您不能在 CTE 中执行此操作。 - AdaTheDev

我原以为这个任务是CTE的一部分，一开始没有注意到你把“定义”放在了与WITH相同的行上。我感到非常惭愧。对不起。 - Andriy M

@Andriy M - 不需要道歉 :) - AdaTheDev

Answer 2

我曾几次不得不采取以下方法作为解决方法（例如在输入数据表达式中使用R服务器进行t-sql的工作。请参见https://learn.microsoft.com/en-us/sql/advanced-analytics/tutorials/quickstart-r-inputs-and-outputs?view=sql-server-2017）。以下是该示例:

WITH ctename (varname) as (
    SELECT MAX(somevar) FROM sometable
)
SELECT *
    FROM someothertable a
    JOIN ctename b on 1=1
    WHERE a.col1 > b.varname;

- Brian Goodwin

Answer 3

我知道这是一个旧问题，但是可以使用CTE和巧妙的CROSS JOIN应用来近似“变量”行为，以利用基于集合的处理的能力。

WITH CTE_Name (somevar1, somevar2)
AS (SELECT 0.15, 0.65)
SELECT
    sq.Col1 * cte.somevar1 AS Calc1,
    sq.Col2 * cte.somevar2 AS Calc2
FROM (
    SELECT 10 Col1, 20 Col2
    UNION SELECT 30, 40
) AS sq
CROSS JOIN CTE_Name AS cte

请注意，使用CROSS JOIN返回连接中涉及的表的笛卡尔积，因此为避免最终输出中出现过多/重复行，CTE必须仅返回一条记录。

- Bandic00t