Sử dụng CTE trong SQL Server

Chuyển qua dự án mới, tôi đã bắt đầu dùng CTE nhiều hơn trong các câu truy vấn của mình, mặc dù đã biết đến nó từ lâu, lý do chính tôi dùng là để câu truy vấn dễ đọc hơn đôi chút.

Ta có thể định nghĩa 1 CTE trong View, Stored Procedure, Function hay Trigger.

Trong ví dụ dưới đây tôi khai báo 2 cái CTE liên tục, cái sau là cteTemp gọi đến cái trước là cte.

 
   WITH cte
          AS ( SELECT  DISTINCT
                        P.s_Syn_SportName ,
                        E.productmanufacturer
               FROM     dbo.Products P WITH ( NOLOCK )
                        JOIN dbo.Products_Extended E WITH ( NOLOCK ) ON e.ProductID = p.ProductID
                        JOIN aqt_manufacturer F WITH ( NOLOCK ) ON E.productmanufacturer = F.strmanufacturer
                  --WHERE     E.productmanufacturer = '2XU'
                        
             ),
        cteTemp
          AS ( SELECT   T1.productmanufacturer ,
                        STUFF(( SELECT  ',' + T2.s_Syn_SportName
                                FROM    cte T2
                                WHERE   T1.productmanufacturer = T2.productmanufacturer
                              FOR
                                XML PATH('')
                              ), 1, 1, '') AS SportName
               FROM     cte T1
               GROUP BY T1.productmanufacturer
             )

Ta có thể chỉ định các column cho CTE

 
WITH C(orderyear, custid) AS
(
SELECT YEAR(orderdate), custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

Một trường hợp khác về CTE mà người ta thường đề cập là dùng CTE để gọi đệ quy. Như ví dụ dưới đây, một CTE gọi đến chính nó, dùng để tìm một nhân viên có id là 2 và tất cả các nhân viên cấp dưới, ví dụ này thường được thấy trên các bài viết trên internet về CTE:

 
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 2
UNION ALL
SELECT C.empid, C.mgrid, C.firstname, C.lastname
FROM EmpsCTE AS P
JOIN HR.Employees AS C
ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

empid mgrid firstname lastname
----------- ----------- ---------- --------------------
2 1 Don Funk
3 2 Judy Lew
5 2 Sven Buck
6 5 Paul Suurs
7 5 Russell King
9 5 Zoya Dolgopyatova
4 3 Yael Peled
8 3 Maria Cameron

Để tìm hiểu thêm CTE, tham khảo tại đây:http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx