SQL 통계_ 서브쿼리 Sub Query

서브쿼리 Sub Query

SQL문장의 하부절(메인 쿼리의 SELECT 하부의 FROM절이나 WHERE절)에 쿼리문을 사용하는 것. 즉, 쿼리안에 쿼리가 포함된 상태. 

1. FROM절 sub Query.

e.g

SELECT p.*,c.*

FROM Products p,

(SELECT category,type FROM Promotion WHERE Active=1)c

WHERE p.Category=c.category

SELECT a.prvn_name, a.distc_name, a.attrc_name,figure
FROM ( 
SELECT p.prvn_name, d.distc_name, a.attrc_name, ROUND(SUM(f.native_cnt)) as figure
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd
JOIN attraction a ON d.prvn_cd = a.prvn_cd AND d.distc_cd = a.distc_cd 
JOIN figure f ON a.prvn_cd = f.prvn_cd AND a.distc_cd = f.distc_cd AND a.attrc_cd = f.attrc_cd
WHERE p.prvn_name = '경기도' AND SUBSTR(f.basis_date,1,4) = '2017' 
GROUP BY a.attrc_cd, p.prvn_cd ,d.distc_cd
ORDER BY d.distc_cd, figure desc
)a