Common Table Expression

CTE هي اضافة جديدة الي t-sql 2005

تستخدم في :

1 - تبسيط الاستعلامات المعقدة

2 - عمل recursive query

صيغة ال cte

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
		expression_name [ ( column_name [ ,...n ] ) ]
	AS
		( CTE_query_definition )

مثال

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
	SELECT ManagerID, COUNT(*) 
	FROM HumanResources.Employee AS e
	WHERE ManagerID IS NOT NULL
	GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

مثال علي ال recursion

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
	SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
	FROM HumanResources.Employee
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
	FROM HumanResources.Employee e
		INNER JOIN DirectReports d
		ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports;
GO

شرح تفصيلي لل CTE

Common Table Expression

مواضيع مشابهة