-- [SQL-6-2-1-a] SELECT t1.shop_id ,CASE WHEN t1.shop_id IN ('S001','S002') THEN 'Star' ELSE 'Others' END shop_dv ,t1.ord_dtm ,t1.ord_no ,t1.ord_amt FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230330','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230331','%Y%m%d') ORDER BY t1.shop_id; -- [SQL-6-2-1-b] SELECT CASE WHEN t1.shop_id IN ('S001','S002') THEN 'Star' ELSE 'Others' END shop_dv ,SUM(t1.ord_amt) ord_amt_sum FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230330','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230331','%Y%m%d') GROUP BY CASE WHEN t1.shop_id IN ('S001','S002') THEN 'Star' ELSE 'Others' END ORDER BY shop_dv; -- [SQL-6-2-1-c] SELECT CASE WHEN t1.shop_id IN ('S001','S002') THEN 'Star' ELSE 'Others' END shop_dv ,SUM(t1.ord_amt) ord_amt_sum FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230330','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230331','%Y%m%d') GROUP BY shop_dv ORDER BY shop_dv; -- [SQL-6-2-1-d] SELECT t1.ord_no ,t1.ord_dtm ,DATE_FORMAT(t1.ord_dtm,'%H') 주문시간 ,CASE WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '07' THEN '7시이전주문' WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '09' THEN '7시부터9시주문' ELSE '9시이후주문' END 주문시간대구분 FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20231101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20231201','%Y%m%d') ORDER BY t1.ord_dtm ASC; -- [SQL-6-2-1-e] -- (방법1) -- GROUP BY 적용후 SQL, CASE를 GROUP BY 항목으로 사용 SELECT CASE WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '07' THEN '7시이전주문' WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '09' THEN '7시부터9시이전주문' ELSE '9시이후주문' END 주문시간대구분 ,COUNT(*) 주문시간대별주문건수 FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20231101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20231201','%Y%m%d') GROUP BY CASE WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '07' THEN '7시이전주문' WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '09' THEN '7시부터9시이전주문' ELSE '9시이후주문' END -- GROUP BY에서 컬럼의 별칭을 정의하지 않도록 주의 ORDER BY 주문시간대구분 ASC; -- (방법2) -- CASE 처리한 별칭을 GROUP BY에 사용 SELECT CASE WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '07' THEN '7시이전주문' WHEN DATE_FORMAT(t1.ord_dtm,'%H') < '09' THEN '7시부터9시이전주문' ELSE '9시이후주문' END 주문시간대구분 ,COUNT(*) 주문시간대별주문건수 FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20231101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20231201','%Y%m%d') GROUP BY 주문시간대구분 -- GROUP BY에 SELECT 절 컬럼의 별칭을 사용(DBMS에 따라 사용 불가) ORDER BY 주문시간대구분 ASC; -- [SQL-6-2-2-a] SELECT t1.shop_id ,SUM(t1.ord_amt) ord_amt_sum ,CASE WHEN SUM(t1.ord_amt) >= 300000 THEN '30만이상' WHEN SUM(t1.ord_amt) >= 200000 THEN '20만이상' ELSE '20만미만' END ord_amt_sum_dv FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230201','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') GROUP BY t1.shop_id ORDER BY ord_amt DESC; -- [SQL-6-2-3-a] 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('20231224','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20231225','%Y%m%d') AND t1.shop_id = 'S100' ORDER BY CASE WHEN t1.mbr_id = 'M1996' THEN 0 ELSE 1 END ASC ,t1.ord_amt DESC;

6. CASE

6-1. CASE 기본 사용법

6-2. 신출귀몰한 CASE