SQL
공통 표 표현식

통사론

• WITH QueryName [(ColumnName, ...)] AS (
  고르다 ...
  )
  SELECT ... FROM QueryName ...;

• RECURSIVE QueryName [(ColumnName, ...)] AS (
  고르다 ...
  UNION [전체]
  SELECT ... FROM QueryName ...
  )
  SELECT ... FROM QueryName ...;

비고

임시 쿼리

이것은 중첩 된 하위 쿼리와 다른 방식으로 동작합니다.

WITH ReadyCars AS (
  SELECT *
  FROM Cars
  WHERE Status = 'READY'
)
SELECT ID, Model, TotalCost
FROM ReadyCars
ORDER BY TotalCost;

동등한 하위 쿼리 구문

SELECT ID, Model, TotalCost
FROM (
  SELECT *
  FROM Cars
  WHERE Status = 'READY'
) AS ReadyCars
ORDER BY TotalCost

재귀 적으로 나무에 올라간다.

WITH RECURSIVE ManagersOfJonathon AS (
    -- start with this row
    SELECT *
    FROM Employees
    WHERE ID = 4

    UNION ALL

    -- get manager(s) of all previously selected rows
    SELECT Employees.*
    FROM Employees
    JOIN ManagersOfJonathon
        ON Employees.ID = ManagersOfJonathon.ManagerID
)
SELECT * FROM ManagersOfJonathon;

값을 생성하다

대부분의 데이터베이스에는 임시 사용을위한 일련의 숫자를 생성하는 원래 방법이 없습니다. 그러나 공용 테이블 표현식을 재귀와 함 2 사용하여 해당 유형의 함수를에 D 레이트 할 수 있습니다.

다음 예제에서는 숫자 1 - 5의 행이있는 열 i 를 사용하여 Numbers 라는 공통 테이블 식을 생성합니다.

--Give a table name `Numbers" and a column `i` to hold the numbers
WITH Numbers(i) AS (
    --Starting number/index
    SELECT 1
    --Top-level UNION ALL operator required for recursion
    UNION ALL
    --Iteration expression:
    SELECT i + 1
    --Table expression we first declared used as source for recursion
    FROM Numbers
    --Clause to define the end of the recursion
    WHERE i < 5
)
--Use the generated table expression like a regular table
SELECT i FROM Numbers;

이 방법은 임의의 번호 간격 및 다른 유형의 데이터와 함께 사용할 수 있습니다.

재귀 적으로 하위 트리를 열거 함

WITH RECURSIVE ManagedByJames(Level, ID, FName, LName) AS (
    -- start with this row
    SELECT 1, ID, FName, LName
    FROM Employees
    WHERE ID = 1

    UNION ALL

    -- get employees that have any of the previously selected rows as manager
    SELECT ManagedByJames.Level + 1,
           Employees.ID,
           Employees.FName,
           Employees.LName
    FROM Employees
    JOIN ManagedByJames
        ON Employees.ManagerID = ManagedByJames.ID

    ORDER BY 1 DESC   -- depth-first search
)
SELECT * FROM ManagedByJames;

재귀 CTE가있는 Oracle CONNECT BY 기능

오라클의 CONNECT BY 기능은 SQL 표준 재귀 CTE를 사용할 때 내장되지 않은 많은 유용하고 중요한 기능을 제공합니다. 이 예제는 SQL Server 구문을 사용하여 이러한 기능을 복제합니다 (완전성을 위해 몇 가지 추가 사항이 있음). 오라클 개발자는 다른 데이터베이스의 계층 적 쿼리에서 누락 된 많은 기능을 찾는데 가장 유용하지만 일반적으로 계층 적 쿼리로 수행 할 수있는 것을 보여 주기도합니다.

  WITH tbl AS (
       SELECT id, name, parent_id
         FROM mytable)
     , tbl_hierarchy AS (
       /* Anchor */
       SELECT 1 AS "LEVEL"
            --, 1 AS CONNECT_BY_ISROOT
            --, 0 AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , 0 AS CONNECT_BY_ISCYCLE
            , '/' + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , t.id AS root_id
            , t.*
         FROM tbl t
        WHERE t.parent_id IS NULL                            -- START WITH parent_id IS NULL
       UNION ALL
       /* Recursive */
       SELECT th."LEVEL" + 1 AS "LEVEL"
            --, 0 AS CONNECT_BY_ISROOT
            --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%' THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
            , th.SYS_CONNECT_BY_PATH_id   + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , th.root_id
            , t.*
         FROM tbl t
              JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
        WHERE th.CONNECT_BY_ISCYCLE = 0)                     -- NOCYCLE
SELECT th.*
     --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
  FROM tbl_hierarchy th
       JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
 ORDER BY th.SYS_CONNECT_BY_PATH_name;                       -- ORDER SIBLINGS BY name

CONNECT BY 기능 위에 설명 된 설명과 함께 :

• 조항
  • CONNECT BY : 계층 구조를 정의하는 관계를 지정합니다.
  • START WITH : 루트 노드를 지정합니다.
  • ORDER SIBLINGS BY : 주문 결과가 올바르게 표시됩니다.
• 매개 변수
  • NOCYCLE : 루프가 감지되면 분기 처리를 중지합니다. 유효한 계층 구조는 Directed Acyclic Graphs이며 순환 참조가이 구문을 위반합니다.
• 연산자
  • PRIOR : 노드의 부모로부터 데이터를 얻습니다.
  • CONNECT_BY_ROOT : 노드의 루트에서 데이터를 확보합니다.
• 의사 열
  • LEVEL : 루트에서 노드까지의 거리를 나타냅니다.
  • CONNECT_BY_ISLEAF : 자식이없는 노드를 나타냅니다.
  • CONNECT_BY_ISCYCLE : 순환 참조가있는 노드를 나타냅니다.
• 기능들
  • SYS_CONNECT_BY_PATH : 루트에서 노드에 대한 경로의 병합 / 연결 표현을 반환합니다.

재귀 적으로 날짜 생성, 예를 들어 팀 로스터 링을 포함하도록 확장

DECLARE @DateFrom DATETIME = '2016-06-01 06:00'
DECLARE @DateTo DATETIME = '2016-07-01 06:00'
DECLARE @IntervalDays INT = 7

-- Transition Sequence = Rest & Relax into Day Shift into Night Shift
-- RR (Rest & Relax) = 1
-- DS (Day Shift) = 2
-- NS (Night Shift) = 3

;WITH roster AS
(
   SELECT @DateFrom AS RosterStart, 1 AS TeamA, 2 AS TeamB, 3 AS TeamC
   UNION ALL
   SELECT DATEADD(d, @IntervalDays, RosterStart),
          CASE TeamA WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamA,
          CASE TeamB WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamB,
          CASE TeamC WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamC
   FROM roster WHERE RosterStart < DATEADD(d, -@IntervalDays, @DateTo)
)

SELECT RosterStart,
       ISNULL(LEAD(RosterStart) OVER (ORDER BY RosterStart), RosterStart + @IntervalDays) AS RosterEnd,
       CASE TeamA WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamA,
       CASE TeamB WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamB,
       CASE TeamC WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamC
FROM roster

결과

즉 1 주째 TeamA는 R & R에, TeamB는 Day Shift에, TeamC는 Night Shift에 있습니다.

공통 테이블 식을 사용하도록 쿼리 리팩토링

총 판매량이 20보다 큰 모든 제품 카테고리를 얻고 싶다고 가정 해 보겠습니다.

다음은 공통 테이블 표현식이없는 쿼리입니다.

SELECT category.description, sum(product.price) as total_sales
FROM sale
LEFT JOIN product on sale.product_id = product.id
LEFT JOIN category on product.category_id = category.id
GROUP BY category.id, category.description
HAVING sum(product.price) > 20

공통 테이블 표현식을 사용하는 동일한 쿼리 :

WITH all_sales AS (
  SELECT product.price, category.id as category_id, category.description as category_description
  FROM sale
  LEFT JOIN product on sale.product_id = product.id
  LEFT JOIN category on product.category_id = category.id
)
, sales_by_category AS (
  SELECT category_description, sum(price) as total_sales
  FROM all_sales
  GROUP BY category_id, category_description
)
SELECT * from sales_by_category WHERE total_sales > 20

공통 테이블식이있는 복합 SQL의 예

"최상위 범주"에서 "가장 저렴한 제품"을 쿼리하려고한다고 가정합니다.

다음은 공통 테이블 표현식을 사용하는 쿼리의 예입니다.

-- all_sales: just a simple SELECT with all the needed JOINS
WITH all_sales AS (
  SELECT
  product.price as product_price,
  category.id as category_id,
  category.description as category_description
  FROM sale
  LEFT JOIN product on sale.product_id = product.id
  LEFT JOIN category on product.category_id = category.id
)
-- Group by category
, sales_by_category AS (
  SELECT category_id, category_description,
  sum(product_price) as total_sales
  FROM all_sales
  GROUP BY category_id, category_description
)
-- Filtering total_sales > 20
, top_categories AS (
  SELECT * from sales_by_category WHERE total_sales > 20
)
-- all_products: just a simple SELECT with all the needed JOINS
, all_products AS (
  SELECT
  product.id as product_id,
  product.description as product_description,
  product.price as product_price,
  category.id as category_id,
  category.description as category_description
  FROM product
  LEFT JOIN category on product.category_id = category.id
)
-- Order by product price
, cheapest_products AS (
  SELECT * from all_products
  ORDER by product_price ASC
)
-- Simple inner join 
, cheapest_products_from_top_categories AS (
  SELECT product_description, product_price
  FROM cheapest_products
  INNER JOIN top_categories ON cheapest_products.category_id = top_categories.category_id
)
--The main SELECT
SELECT * from cheapest_products_from_top_categories