CTE ( Common Table Expression )을 통해 공통테이블 식을 사용해서 임시로 테이블 정의/재활용 ==> WITH문
| with 테이블이름 as (select ~ from ) select ~ |
with v1 as (
select eid as eid, atvy_type, ingrs as ingrs, count(*) as cnt1, count(distinct(key1)) as cnt2,
count(link_type)as cnt3
from table1
where 1=1
group by eid, atvy_type, ingrs, link_type
)
select v1.eid, e3.title, atvy_type, v1.ingrs,c.comn_cd_nm, v1.cnt1, cnt2, cnt3
from v1 inner join table3 as e3
on v1.eid=e3.eid
left outer join table2 as c
on v1.ingrs=c.comn_cd
order by 1,2,3,4;
v1을 생성해서
v1결과값과 후행 테이블을 join 해서 결과값 출력
result
c1 = 전체합계(null포함) , c2 = 중복제거 , c3 = 추가조건
| eid | title | atvy_type | ingrs | comn_cd_nm | cnt1 | cnt2 | cnt3 |
| 12345 | 제목1 | AB-DATA | TYPE1 | 메인 | 3 | 1 | 0 |
| 12345 | 제목1 | AB-DATA | TYPE2 | 상세 | 7 | 1 | 0 |
| 54321 | 제목2 | AB-DATA | TYP1 | 메인 | 3 | 2 | 1 |
| 54321 | 제목2 | AB-DATA | TYP3 | 상단 | 2 | 1 | 1 |
https://wiki.postgresql.org/wiki/CTEReadme
CTEReadme - PostgreSQL wiki
Usage Definition The WITH clause allows to define a table expression being valid within a same SELECT statement. The table expression is called "Common Table Expression"(CTE). The use case for CTE is similar to VIEW but it is more handy than VIEW. Unlike V
wiki.postgresql.org
'IT > SQL' 카테고리의 다른 글
| [mysql] insert select 구문 (0) | 2022.01.17 |
|---|---|
| [PostgreSQL] 마지막 한달이내 접속일자 (0) | 2021.12.27 |
| [PostgreSQL] 월별,시간별 데이터 집계 (0) | 2021.12.27 |
| [PostgreSQL] [limit] 분할 삭제,분할 업데이트 (0) | 2021.12.27 |
| [PostgreSQL] row_number() 행번호 (0) | 2021.12.27 |