잊지말자 sql

IFNULL (값이 없는 경우 쓸 수 있다)

select PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, "NONE") as TLNO
from PATIENT
where AGE <= 12 and GEND_CD = 'W'
order by AGE desc, PT_NAME

 

 

LIMIT N (레코드의 갯수를 제한 할 때 쓴다)

select a.FLAVOR
from FIRST_HALF a
join (
    select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
    from JULY
    group by FLAVOR
) b on a.FLAVOR = b.FLAVOR
order by a.TOTAL_ORDER + b.TOTAL_ORDER desc
limit 3

 

 

inner join, left outer join, right outer join, full outer join

1. 이너조인

 

2. 레프트 아우터 조인

 

3. 라이트 아우터 조인

4. 풀 아우터 조인

DATEDIFF (두 날짜 사이의 거리 구하기)

-- 코드를 입력하세요
select HISTORY_ID, CAR_ID, date_format(start_date, '%Y-%m-%d') as START_DATE, date_format(end_date, '%Y-%m-%d') END_DATE, (
    case
        when DATEDIFF(end_date, start_date) >= 29 then '장기 대여'
        else '단기 대여'
    end
) as RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where DATE_FORMAT(start_date, "%Y-%m") = '2022-09'
order by history_id desc

 

 

ROUND, TRUNCATE (소수점 처리)

select CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) as AVERAGE_DURATION
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by CAR_ID
having AVERAGE_DURATION >= 7
order by AVERAGE_DURATION desc, CAR_ID desc