我们如何在SQL Server中的子查询中使用CTE?
例如:
SELECT id (我想在这里使用CTE), name FROM table_name
例如:
SELECT id (我想在这里使用CTE), name FROM table_name
在顶部定义你的CTE,然后在子查询中访问它即可。
WITH YourCTE(blubb) AS
(
SELECT 'Blubb'
)
SELECT id,
(SELECT blubb FROM YourCTE),
name
FROM table_name
WITH
语句,因此这样做没有意义。 - Silencer它不起作用:
select id (I want to use CTE here), name from table_name
在子查询中无法使用公用表表达式(CTE)。
您可以将其视为一种解决方法:
CREATE VIEW MyCTEView AS ..here comes your CTE-Statement.
那么你就能够做到这件事:
select id (select id from MyCTEView), name from table_name
CREATE VIEW [dbo].[_vEmployees]
AS
WITH
TEST_CTE(EmployeeID, FirstName, LastName, City, Country)
AS (
SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 4
),
TEST_CTE2
AS (
SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 7
)
SELECT EmployeeID, FirstName, LastName, City, Country FROM TEST_CTE UNION SELECT * FROM TEST_CTE2
GO
现在,将其用于子查询中。SELECT * FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM _vEmployees)