在SQL Server 2005中,可以通过使用CTE来简化T-SQL。
CTE允许我们不使用View,子查询,或者临时表的方式,来分离T-SQL。
1。开始阶段定义CTE
2。在主查询中使用CTE,CTE可以看作一个Table Or View.
[ WITH <common_table_expression> [ , ...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ , ...n ] ) ]
AS
( CTE_query_definition )
一个简单例子:
WITH CustomerTable AS
(
SELECT *
FROM dbo.Customers AS e
)
SELECT *
FROM CustomerTable
WITH CustomerTable (totalcount) AS
(
SELECT count(*)
FROM dbo.Customers AS e
)
SELECT *
FROM CustomerTable