SQL 기본(8): 공통 테이블(CommonTable) AS CTE
1. 공통 테이블
-데이터베이스에 없는 테이블이 필요할 때 사용
-일반 공통 테이블 / 재귀 공통 테이블으로 식 구분
-다음에 나오는 SELECT 문에만 사용 가능
-쿼리 실행 중 메모리에 존재하는 테이블
WITH [CTE_TABLE] <col> AS (
SELECT <col> FROM [TABLE] )
SELECT <col> FROM [CTE_TABLE];
-WITH 문 입력 후 테이블 이름 지정, 사용할 열 지정(열 NAME: AS 다음에 오는 SELECT 문이 반환하는 열)
-쿼리문 작성
-CTE에서 정의한 열의 개수와 CTE SELECT 문에서 얻은 열 목록이 다르면 오류 발생
2. 재귀 CTE:
| 재귀 CTE: CTE 결과를 CTE 내부 쿼리에서 재사용, 계층 데이터 검색할 때 사용, 2개 이상의 쿼리 필요 WITH[CTE_TABLE] (<col>, <col>...) AS(---(쿼리1 앵커멤버) <SELECT * FROM [TABLE]A> UNIOLL ALL --(쿼리2 재귀멤버) <SELECT * FROM [TABLE]B JOIN CTE_TABLE>) SELECT * FROM[CTE_TABLE]; |
| 1. 쿼리 1 실행, 쿼리 2 기본값 0으로 초기화-쿼리 2 실행(기본값 1만큼 증가) 2. 쿼리 1 결과 행 만큼 쿼리 2에서 CTE_TABLE 이름 호출-쿼리 2 기본값 1씩 증가하며 쿼리 1 결과 행수 도달 3.결과가 없으면 호출 중단, 외부 SELECT 문에 과정 ㅂ 통해 만든 CTE 누적 결과 검색 |
| 앵커 멤버: 재귀 CTE의 첫 번째 호출로, 집합 연산자로 연결된 하나 이상의 쿼리 정의 다른 CTE를 참조하지 않는 경우 앵커 멤버로 간주 |
| 재귀 멤버: CTE를 참조하는 UNION ALL로 연결된 하나 이상의 쿼리 정의 이전 호출에서 반환되는 행이 없을 때 재귀를 암묵적으로 종료 |
--CTE 생성, 컬럼별 갯수가 같아야함 / 임시테이블로 항상 같이 사용해야 표기
WITH cte_stock_price (date, symbol, [close])
AS (
SELECT date, symbol, [close] FROM DoItSQL.DBO.stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
UNION ALL
SELECT date, symbol, [close] FROM DoItSQL.DBO.stock
WHERE date >= '2021-02-01' AND date <= '2021-02-07'
)
SELECT * FROM cte_stock_price WHERE symbol = N'MSFT'
WITH cte_stock_price (date, symbol, [close])
AS
(
SELECT date, symbol, [close] FROM DoItSQL.DBO.stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
), cte_symbol(symbol, company_name)
AS
(
SELECT symbol, company_name FROM DoItSQL.DBO.nasdaq_company
)
SELECT * FROM cte_stock_price WHERE symbol IN(SELECT symbol FROM cte_symbol WHERE company_name like '%M')
WITH CTE AS
(
--앵커멤버
SELECT 1 AS NUM
UNION ALL
--재귀멤버
SELECT NUM + 2
FROM CTE
WHERE NUM + 2 <= 200
)
SELECT * FROM CTE
--FACTORIAL 구현하기
WITH CTE_FACT (NUM, FACT_SUM) AS
(
SELECT 1 AS NUM, 1 AS FACT_NUM
UNION ALL
SELECT NUM +1, FACT_SUM*(NUM+1) FROM CTE_FACT WHERE NUM <10
)
SELECT * FROM CTE_FACT