SET

• Update 기능과 많이 쓰이는 문법

#예시

SET @HOUR = -1;#변수 설정
SELECT @HOUR := @HOUR+1 as HOUR,#0부터 23까지를 hour값으로 설정
(SELECT COUNT(*)
 FROM animal_outs
 where @HOUR = HOUR(DATETIME)) as COUNT#hour값에 따른 count를 구함
FROM animal_outs
where @HOUR<23;#hour를 23으로 막음

조건문 (CASE WHEN, IF)

• CASE WHEN

--CASE사용법--
CASE WHEN 조건절 THEN 참일때 값 ELSE 거짓일때 값 END 컬럼명

SELECT DISTINCT
GENDER,
CASE WHEN GENDER = '001' THEN '여' ELSE '남' END AS 성별
FROM MY_TABLE

--다중 CASE WHEN--
SELECT *,
   (CASE WHEN SCORE>= '90' THEN 'A학점'
        WHEN (SCORE>= '80' AND SCORE < '90') THEN 'B학점'
        WHEN (SCORE>= '70' AND SCORE < '80') THEN 'C학점' 
        WHEN (SCORE>= '60' AND SCORE < '70') THEN 'D학점'
        ELSE 'F학점'
    END) AS '학점'
FROM MY_TABLE

• IF

select member_id, if ( isnull(birthday), '-', birthday ) from member

WITH ~ AS

• 하나 이상의 서브쿼리에서 반환된 데이터를 단일 쿼리에서 재사용할 수 있음
• 코드 중복을 줄이고 가독성을 높일 수 있음

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    CASE 
      WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')   

SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC