-- [SQL-8-2-1-a] SELECT x.shop_id FROM startdbmy.tr_ord x WHERE x.ord_dtm >= STR_TO_DATE('20190317','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20190318','%Y%m%d'); -- [SQL-8-2-1-b] SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE t1.shop_id IN ( -- [SQL-8-2-1-a] SELECT x.shop_id FROM startdbmy.tr_ord x WHERE x.ord_dtm >= STR_TO_DATE('20190317','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20190318','%Y%m%d') ); -- [SQL-8-2-1-c] SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE t1.shop_id = (-- [SQL-8-2-1-a] SELECT x.shop_id FROM startdbmy.tr_ord x WHERE x.ord_dtm >= STR_TO_DATE('20190317','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20190318','%Y%m%d') ); -- [SQL-8-2-1-d] SELECT t1.ord_no ,t1.ord_dtm ,t1.shop_id FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220201','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220301','%Y%m%d') AND t1.shop_id = (SELECT x.shop_id FROM startdbmy.ms_shop x ORDER BY x.shop_size DESC LIMIT 1) ORDER BY t1.ord_no ASC; -- [SQL-8-2-2-a] SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE t1.shop_oper_tp ='DIST' AND t1.chair_qty > 70; -- [SQL-8-2-2-b] SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE t1.shop_oper_tp ='DIST' AND t1.chair_qty > 70 AND EXISTS( SELECT * FROM startdbmy.tr_ord X WHERE x.shop_id = t1.shop_id -- > 메인쿼리의 shop_id를 받아서 처리 AND x.ord_dtm >= STR_TO_DATE('20210101','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20210201','%Y%m%d') ) ORDER BY t1.shop_id; -- [SQL-8-2-2-c] SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE t1.shop_oper_tp ='DIST' AND t1.chair_qty > 70 AND NOT EXISTS( SELECT * FROM startdbmy.tr_ord X WHERE x.shop_id = t1.shop_id -- > 메인쿼리의 shop_id를 받아서 처리 AND x.ord_dtm >= STR_TO_DATE('20210101','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20210201','%Y%m%d') ) ORDER BY t1.shop_id; -- [SQL-8-2-2-d] IN 서브쿼리 SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE t1.shop_id IN ( SELECT x.shop_id FROM startdbmy.tr_ord x WHERE x.ord_dtm >= STR_TO_DATE('20190320','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20190321','%Y%m%d')) ORDER BY t1.shop_id; -- [SQL-8-2-2-e] 같은 결과를 만드는 EXISTS 서브쿼리 -- IN 조건 컬럼을 서브쿼리 안으로 밀어 넣어서 연결 조건으로 사용 SELECT t1.shop_id ,t1.shop_nm FROM startdbmy.ms_shop t1 WHERE EXISTS -- > IN을 EXISTS로 변경(조건 컬럼은 서브쿼리 안에서 처리) ( SELECT x.shop_id FROM startdbmy.tr_ord x WHERE x.ord_dtm >= STR_TO_DATE('20190320','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20190321','%Y%m%d') AND x.shop_id = t1.shop_id) -- > IN 조건 컬럼 이동 ORDER BY t1.shop_id; -- [SQL-8-2-3-a] -- EXISTS를 사용해 2020년 5월에 가입한 PLAT 등급 회원의 주문만 필터 SELECT t1.ord_no ,t1.ord_dtm, t1.mbr_id ,t1.ord_amt FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20240610','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240611','%Y%m%d') AND EXISTS( SELECT * FROM startdbmy.ms_mbr x WHERE x.mbr_id = t1.mbr_id AND x.join_dtm >= STR_TO_DATE('20200501','%Y%m%d') AND x.join_dtm < STR_TO_DATE('20200601','%Y%m%d') AND x.mbr_gd = 'PLAT') ORDER BY t1.mbr_id ,t1.ord_no; -- [SQL-8-2-3-b] -- 조인을 사용해 2020년 5월에 가입한 PLAT 등급 회원의 주문만 조회 SELECT t1.ord_no ,t1.ord_dtm, t1.mbr_id ,t1.ord_amt ,t2.nick_nm ,t2.mbr_gd ,t2.join_dtm FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_mbr T2 ON (t2.mbr_id = t1.mbr_id) WHERE t1.ord_dtm >= STR_TO_DATE('20240610','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240611','%Y%m%d') AND t2.join_dtm >= STR_TO_DATE('20200501','%Y%m%d') AND t2.join_dtm < STR_TO_DATE('20200601','%Y%m%d') AND t2.mbr_gd = 'PLAT' ORDER BY t1.mbr_id ,t1.ord_no; -- [SQL-8-2-3-c] SELECT t1.shop_id ,t1.shop_nm, t1.chair_qty FROM startdbmy.ms_shop t1 WHERE t1.shop_oper_tp ='DIST' AND t1.chair_qty >= 75 AND EXISTS( SELECT * FROM startdbmy.tr_ord X WHERE x.shop_id = t1.shop_id AND x.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20220201','%Y%m%d') ) ORDER BY t1.shop_id; -- [SQL-8-2-3-d] SELECT t1.shop_id ,t1.shop_nm, t1.chair_qty FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord T2 ON (t2.shop_id = t1.shop_id) WHERE t1.shop_oper_tp ='DIST' AND t1.chair_qty >= 75 AND t2.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20220201','%Y%m%d') ORDER BY t1.shop_id; -- [SQL-8-2-4-a] SELECT t1.shop_id ,t1.shop_nm, t1.shop_size FROM startdbmy.ms_shop t1 WHERE t1.shop_oper_tp ='FLAG' AND t1.shop_size >= 60 AND EXISTS( SELECT 1 -- > HAVING 절에서 집계함수 처리시, SELECT 절에 *를 사용할 수 없습니다. FROM startdbmy.tr_ord X WHERE x.shop_id = t1.shop_id AND x.ord_dtm >= STR_TO_DATE('20201201','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20210101','%Y%m%d') HAVING COUNT(*)>=30 -- > HAVING COUNT 조건 사용 ) ORDER BY t1.shop_id; -- [SQL-8-2-4-b] SELECT t1.shop_oper_tp ,( SELECT MAX(x.base_cd_nm) FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'shop_oper_tp' AND x.base_cd = t1.shop_oper_tp) shop_oper_tp_nm ,COUNT(*) shop_cnt FROM startdbmy.ms_shop t1 WHERE EXISTS( SELECT * FROM startdbmy.tr_ord x INNER JOIN startdbmy.tr_ord_det y ON (x.ord_no = y.ord_no) INNER JOIN startdbmy.ms_item z ON (z.item_id = y.item_id) WHERE x.shop_id = t1.shop_id AND x.ord_dtm >= STR_TO_DATE('20201201','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20210101','%Y%m%d') AND Z.item_cat = 'BKR' ) GROUP BY t1.shop_oper_tp ORDER BY t1.shop_oper_tp; -- [SQL-8-2-5-a] -- 22년 2월1일부터, 22년 2월 4일까지 매장별 마지막 ord_no만 조회 SELECT t1.shop_id ,t1.ord_no ord_no_last FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220201','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220204','%Y%m%d') AND t1.ord_no = ( -- > (2)서브쿼리의 MAX(x.ord_no)를 받아서 조건 처리중 SELECT MAX(x.ord_no) FROM startdbmy.tr_ord x WHERE x.ord_dtm >= STR_TO_DATE('20220201','%Y%m%d') AND x.ord_dtm < STR_TO_DATE('20220204','%Y%m%d') AND x.shop_id = t1.shop_id -- > (1)메인쿼리의 t1.shop_id를 받아서 조건 처리중 ) ORDER BY t1.shop_id ASC; -- [SQL-8-2-5-b] SELECT t1.shop_id ,MAX(t1.ord_no) ord_no_last FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220201','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220204','%Y%m%d') GROUP BY t1.shop_id ORDER BY t1.shop_id;

8. SUB QUERY

8-1. SELECT 절 서브쿼리

8-2. WHERE 절 서브쿼리