我们如何在sql server中的子查询中使用CTE？

How we can use CTE in subquery in sql server?

我们如何在sql server中的子查询中使用CTE？

like ..

喜欢 ..

select id (i want to use CTE here), name from table_name

select id（我想在这里使用CTE），来自table_name的名称

3 个解决方案

#1

Just define your CTE on top and access it in the subquery?

只需在顶部定义您的CTE并在子查询中访问它？

WITH YourCTE(blubb) AS
(
    SELECT 'Blubb'
)
SELECT id,
       (SELECT blubb FROM YourCTE),
       name
FROM   table_name

#2

It doesn't work:

它不起作用：

select id (I want to use CTE here), name from table_name

It's not possible to use CTE in sub queries.

在子查询中不可能使用CTE。

You can realize it as a work around:

您可以将其视为一种解决方法：

CREATE VIEW MyCTEView AS ..here comes your CTE-Statement.

Then you are able to do this:

然后你就可以这样做：

select id (select id from MyCTEView), name from table_name

#3

A bit late but for anyone searching for this, another option is to create a function that contains the CTE and then you can use that function in your fields clause.

有点迟了但是对于任何搜索它的人来说，另一种选择是创建一个包含CTE的函数，然后你可以在fields子句中使用该函数。

create function dbo.myCTEFunction(@ID int) returns varchar(100) as
begin
  declare @Result varchar(100)
  -- fill the variable with your CTE here...
  return @Result
end;


select t.id, 
       (select dbo.myCTEFunction(t.id)),
       t.name
from table_name t