Recursive CTE Structure [ ;WITH .... AS (... UNION ALL ...) SELECT ]

Oracle 에서는 어렵지 않던 것이

MSSQL 에서 구현하려면 좀 어려운 것 중의 하나가 Connect by 구문이다. 

이 참에 업무에서 사용했던 쿼리를 참고로 정리해 본다. 

CTE (Common Table Expression)  in MSSQL 

-- Define the CTE  expression name and column lists

;WITH BookingFamily( bookingid, bookingparentid, status, recordlocator, bookingdate, lvl ) 

-- Define the CTE query  

AS (      -- Anchor member is defined [ Recursive 가 시작하는 시점이라고 생각하면 된다. Start with 처럼 ]
            SELECT   BookingID, BookingParentID, Status, RecordLocator,  BookingDate, 0 AS lvl

             FROM    XMSSQLDB.dbo.Booking

            WHERE    BookingID in (421585) 

            UNION ALL 
            -- Recursive member is defined

            SELECT   a.BookingID, a.BookingParentID, a.Status,
                           a.RecordLocator, a.BookingDate, b.lvl + 1 AS lvl

               FROM     XMSSQLDB.dbo.Booking AS a 
            -- ANSI JOIN  and Recursive with BookingFamily

            INNER JOIN   BookingFamily  AS b
            ON a.BookingParentID = b.bookingid

     )

-- Define the outer query referencing the CTE Name "BookingFamily "

 SELECT   TOP (100) PERCENT  
                 bookingid, bookingparentid, status, recordlocator, bookingdate, lvl    
     FROM   BookingFamily 

Divided and the generated in hierachy ( lvl 0 parent, lvl 1 child, lvl 2  child of lvl 1 ... )

bookingid  bookingparentid  status  recordlocator bookingdate  lvl

421585         0                3        Y2KBRL       2009-10-09    0

530590             421585            4        Q6536Z        2009-10-09    1

558714             421585            3        Y7X65J        2009-10-09    1 

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similiar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to;

Create a recursive query.

Substitute for a view when the general use of a view is not required; that is, you do not have to store definition in metadata.

Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complx queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more comples, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views. 

CTE with multiple references 

;with

 cte_Orders

 AS

 (

     SELECT  ID, Description, OrderDate

        FROM  Orders

      WHERE  OrderDate BETWEEN '2011-03-01' AND '2011-03-31'

 )

 ,cte_OrderParts

 AS

 (

     SELECT SUM(UnitPrice * Quantity) AS Amount, Order_ID

        FROM OrderParts

        INNER JOIN cte_Orders
        ON Order_ID = cte_Orders.ID

       GROUP BY Order_ID

 )

 ,cte_OrderLabor

 AS

 (

     SELECT SUM(UnitPrice * Quantity) AS Amount, Order_ID

        FROM  OrderLabor

        INNER JOIN cte_Orders ON Order_ID = cte_Orders.ID

      GROUP BY Order_ID

 )
 

 SELECT

     ID, Description, OrderDate

     ,COALESCE(cte_OrderParts.Amount, 0.00) AS PartsAmount

     ,COALESCE(cte_OrderLabor.Amount, 0.00) AS LaborAmount

 FROM
         cte_Orders

         LEFT JOIN cte_OrderParts ON ID = cte_OrderParts.Order_ID

         LEFT JOIN cte_OrderLabor ON ID = cte_OrderLabor.Order_ID

 This is an example where the cte_Orders was used multiple times. The reason I did it this was is that it will reduce the parts and labor information pulled to just those relevant to the orders we want making it more efficient because it doesn't have to do unecessary calculations.


Reference : http://msdn.microsoft.com/en-us/library/ms190766.aspx