MS-SQL

공통 테이블 식(CTE) 은 단순 쿼리로부터 파생된 임시 결과 집합입니다.
CTE 는 파생된 테이블을 사용하는 것처럼, 동일한 수단으로 사용 되어질 수 있습니다. 
CTE는 또한 자신에 대한 참조를 포함할 수 있습니다. 이는 데이터베이스 개발자가 재귀 쿼리를 작성할 수 있도록 해줍니다.
CTE 는 또한 뷰 대신 사용 되어질 수도 있습니다. 개념적으로는 임시테이블과 비슷합니다.
더 적은 코드로 임시테이블, 재귀호출등을 처리할 수 있습니다.

기존 SQL 2000 에서 사용 하던 방식 

select SalesPersonID, count(*) as NumSales into #tmp 
    from Sales.SalesOrderHeader Group by SalesPersonID

select ts.SalesPersonID, sp.SalesYTD, ts.NumSales
    from Sales.SalesPerson sp inner join #tmp ts 
    on ts.SalesPersonID = sp.SalesPersonID
        order by NumSales Desc

SQL 2005의 CTE 를 이용한 방식 

with TopSales    (SalesPersonID, NumSales) 
as 
    ( Select SalesPersonID, Count(*)
        From Sales.SalesOrderHeader Group BY SalesPersonID)

select ts.SalesPersonID, sp.SalesYTD, ts.NumSales
from Sales.SalesPerson sp inner join TopSales ts
    on ts.SalesPersonID = sp.SalesPersonID
        order by NumSales Desc


1.파생된 테이블로 사용된 CTE 필터링 하기

뷰로 리턴된 결과집합을 필터링하는 것과 마찬가지로, 여러분은 공통 테이블 식(CTE) 안에 포함된 결과 집합을 필터링 할 수 있습니다.

WITH SalesCTE(ProductID , SalesOrderID)
AS 
(
    SELECT ProductID , COUNT(SalesOrderID)
        FROM Sales.SalesOrderDetail
            GROUP BY ProductID
)
SELECT * FROM SalesCTE WHERE SalesOrderID > 50 --50회이상주문된모든제품들

2.CTE 로 집계하기

공통 테이블 식(CTE) 으로 리턴된 데이터를 가지고 집계 또는 그 밖의 다른 계산을 수행하는 것은 기본 테이블로부터 리턴된 데이터를 
가지고 집계 및 기타 계산을 수행하는 것과 유사합니다.

WITH SalesCTE(ProductID , SalesOrderID)
AS 
(
    SELECT ProductID , COUNT(SalesOrderID)
        FROM Sales.SalesOrderDetail
            GROUP BY ProductID
)
SELECT AVG(SalesOrderID) FROM SalesCTE WHERE SalesOrderID > 50

-- 50 회이상주문된모든제품들의평균주문횟수

3. 공통 테이블 식(CTE)을 사용하는 재귀 쿼리

공통 테이블 식(CTE)의 진정한 위력은 재귀 쿼리를 작성하는 데에 있습니다. 
공통 테이블 식(CTE) 이 자기 자신에 대한 참조가 가능하기 때문에, 재귀 쿼리의 작성을 비교적 간단히, 수월하게 할 수 있습니다.

CREATE TABLE CarParts 
(
    CarID    int            NOT NULL
    ,    Part    varchar(15)
    ,    SubPart    varchar(15)
    ,    Qty        int
)
GO
INSERT CarParts VALUES(1 , 'Body' , 'Door' , 4)
INSERT CarParts VALUES(1 , 'Body' , 'Trunk Lid' , 1)
INSERT CarParts VALUES(1 , 'Body' , 'Car Hood' , 1)
INSERT CarParts VALUES(1 , 'Door' , 'Handle' , 1)
INSERT CarParts VALUES(1 , 'Door' , 'Lock' , 1)
INSERT CarParts VALUES(1 , 'Door' , 'Window' , 1)
INSERT CarParts VALUES(1 , 'Body' , 'Rivets' , 1000)
INSERT CarParts VALUES(1 , 'Door' , 'Rivets' , 100)
INSERT CarParts VALUES(1 , 'Door' , 'Mirror' , 1)

자동차의 부품 및 하위 부품에 대한 정보를 가지고 있는 테이블을 만들기 위한 쿼리 입니다. 보통 이러한 정보를 담고 있는 테이블을 
재료표(bill of materials)라고 부릅니다. 재귀적 CTE 는 재료표 , 조직도 및 기타 계층적인 구조를 갖는 데이터를 반환하는 작업을 
쉽게 할 수 있도록 합니다. 

지금 생성하는 테이블 정보에서 Body 와 같은 부품이 여러 하위 부품들을 가지고 있다는 사실을 주목하십시오.. 
예를 들어, Body 부품은 1000 개의 못(rivet) 을 하위 부품으로 가지고 있고, Body 의 하위 부품인 Door 또한 자신(Door) 의 하위 부품으로 
100개의 못(rivet)을 가지고 있습니다.

WITH CarPartsCTE(SubPart , Qty)
AS 
(
    -- 앵커멤버(Anchor Member): 
    -- CarPartsCTE 자신을참조하지않는SELECT 쿼리
    SELECT SubPart , Qty
        FROM CarParts
            WHERE Part = 'Body'
    UNION ALL
    -- 재귀멤버(Recursive Member):
    -- CTE(CarPartsCTE) 자기자신을참조하는SELECT 쿼리
    SELECT CarParts.SubPart    , CarPartsCTE.Qty * CarParts.Qty
        FROM CarPartsCTE INNER JOIN CarParts
            ON CarPartsCTE.SubPart = CarParts.Part
            WHERE CarParts.CarID = 1
)

-- 출력쿼리
SELECT SubPart , SUM(Qty) as q FROM CarPartsCTE GROUP BY SubPart

재귀적 CTE 는 최소한 2개 이상의 쿼리들로 구성 되어집니다. 첫번째 요소로 구성된 쿼리는 CTE 자체를 참조하지 않는 쿼리입니다.
이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버(anchor member)라고 합니다. 두 번째 구성요소는 재귀 
멤버(recursive member) 라고 불리워지는 재귀 쿼리입니다. 이러한 쿼리들은 UNION ALL 연산자에 의해 구별되어 집니다.

자동차의 본체(Body) 를  만들기 위해 필요한 부품들의 총 수량에 대한 리스트를 얻어오는 쿼리 입니다.  
공통 테이블 식(CTE)은 맨 처음  간단한 쿼리(“SELECT SubPart, Qty FROM CarParts WHERE Part = 'Body'”.)로부터 기본 결과 집합을 만듭니다. 
그 다음,  이 부품들의 하위 부품들과  이 하위 부품의 개수(필요한 상위 부품의 개수 *  상위 부품 단위당 필요한 하위 부품 개수)
에 대한 정보를 얻어오는 쿼리문을 UNION ALL 연산자로 결합합니다. 두 번째 쿼리문의 INNER JOIN 문장 안에 CarPartsCTE 라는 이름을 가진 
공통 테이블 식(CTE) 자체에 대한 참조 (재귀 참조)  를 포함하는 것입니다.


재귀 쿼리를 이용한 1~ 10까지 쿼리

WITH TEST AS(
    SELECT IDX = 1 UNION ALL
    SELECT IDX = IDX + 1 FROM TEST WHERE IDX < 10
)SELECT * FROM TEST

4. 프로시져와 CTE를 이용한 트리구조 만드는 예제...

 CREATE PROCEDURE TREE_CALL (@url1 varchar(100)) as
BEGIN 
    ;
    with cte (board_idx,m_name, m_step, m_sex, m_homepage,url_name,m_writeday, m_level , path1)
    as
    (
        select board_idx,m_name, m_step, left(m_jumin2,1) as m_sex, m_homepage,url_name, m_writeday, 1 as m_level,
            convert(varchar(1000), ' > ' + m_homepage) path1 
            from demo005_member where (m_homepage is not null) and (m_homepage<>'') and (m_homepage=@url1)
        union all
        select A.board_idx, A.m_name, A.m_step, left(A.m_jumin2,1) as m_sex, A.m_homepage, A.url_name , A.m_writeday, m_level+1 , 
            convert(varchar(1000), path1 + ' > ' + A.m_homepage)
            from demo005_member A, cte B where a.url_name = b.m_homepage and (A.m_homepage is not null) and (A.m_homepage<>'')
    )
    select board_idx, m_name, m_step, m_sex, m_homepage, url_name , m_writeday, m_level , path1 from cte  order by path1 asc
end