[SQL]-(110) WITH절 사용하기2

---

[Reference] 초보자를 위한 SQL 200제

---

[SQL 문법]

서브 쿼리 2개가 서로 데이터를 참고할 수 있다.

WITH JOB_SUMSAL AS (SELECT JOB, SUM(SAL) total
                        FROM EMP
                        GROUP BY JOB),
     DEPTNO_SUMSAL AS (SELECT DEPTNO, SUM(SAL) total
                             FROM EMP
                             GROUP BY DEPTNO
                             HAVING SUM(SAL) > (SELECT AVG(total) + 3000
                                                  FROM JOB_SUMSAL)
                  )
    SELECT DEPTNO, total
      FROM DEPTNO_SUMSAL

[예시]

# 오라클 연동 및 접속
import pandas as pd
import cx_Oracle
dsn=cx_Oracle.makedsn('localhost',1521,'orcl')
db=cx_Oracle.connect('scott','tiger')
cursor=db.cursor()

# SQL 문법
cursor.execute("""
WITH JOB_SUMSAL AS (SELECT JOB, SUM(SAL) total
                        FROM EMP
                        GROUP BY JOB),
     DEPTNO_SUMSAL AS (SELECT DEPTNO, SUM(SAL) total
                             FROM EMP
                             GROUP BY DEPTNO
                             HAVING SUM(SAL) > (SELECT AVG(total) + 3000
                                                  FROM JOB_SUMSAL)
                  )
    SELECT DEPTNO, total
      FROM DEPTNO_SUMSAL
"""
)

row=cursor.fetchall()
colname=cursor.description
col=[]

for i in colname:
    col.append(i[0])

# pandas를 사용한 데이터 프레임 형식으로 변환
emp=pd.DataFrame(row,columns=col)
print(emp)

[결과]

   DEPTNO  TOTAL
0      30   9400