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
37
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
11
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
0
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
#1
37
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
11
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
0
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