SET
#예시
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사용법--
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
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