IFNULL(컬럼명, null인경우 대체할 값)
case-when
case
when 조건식1 then 결과1
when 조건식2 then 결과2
else 조건식1,2, ... 가 모두 아니면 결과
end
if(컬럼명 is null, '널이다', '널이 아니다')
if(1<2, 'yes', 'no')
nullif(expr1, expr2)
=> expr1=expr2 값을 비교해서 true가 아니면 expr1을 return한다. 만약 true이면 null을 return한다.
https://school.programmers.co.kr/learn/courses/30/lessons/59042
select b.animal_id, b.name
from animal_outs b
left outer join
animal_ins a
on a.animal_id = b.animal_id
where a.animal_id is null
order by b.animal_id, b.name;
두 개의 테이블을 join할 때 오른쪽이 null이 될 수 있도록 만들기 위해서는 left outer join을 사용한다.
https://school.programmers.co.kr/learn/courses/30/lessons/59043
select b.animal_id, b.name
from animal_ins a
join
animal_outs b
on a.animal_id = b.animal_id
where a.datetime > b.datetime
order by a.datetime;
날짜를 비교할 때는 부등호로 비교함
https://school.programmers.co.kr/learn/courses/30/lessons/59044
select a.name, a.datetime
from animal_ins a
left outer join
animal_outs b
on a.animal_id = b.animal_id
where b.animal_id is null
order by a.datetime asc limit 3;
limit을 사용할 수 있는가
https://school.programmers.co.kr/learn/courses/30/lessons/59411
select b.animal_id, b.name
from animal_ins a
join animal_outs b
on a.animal_id = b.animal_id
order by b.datetime - a.datetime desc limit 2;
order by절에는 컬럼만 쓸 수 있는건 아니다.
https://school.programmers.co.kr/learn/courses/30/lessons/77487
SELECT *
from places
where host_id in (select host_id
from places
group by host_id
having count(host_id)>=2)
order by id;
https://school.programmers.co.kr/learn/courses/30/lessons/131113
select order_id, product_id, date_format(out_date, '%Y-%m-%d'),
case
when month(OUT_DATE) is null then '출고미정'
when month(OUT_DATE)<=4 or (month(OUT_DATE)=5 and day(OUT_DATE)=1) then '출고완료'
else '출고대기'
end as '출고여부'
from food_order
order by order_id;
date_format(날짜, '%Y-%m-%d') => 2023-11-17 형태로 나타냄
https://school.programmers.co.kr/learn/courses/30/lessons/131123
# select food_type, max(favorites)
# from rest_info
# group by food_type;
select food_type, rest_id, rest_name, favorites
from rest_info
where (food_type, favorites) in (
select food_type, max(favorites)
from rest_info
group by food_type)
order by food_type desc;
https://school.programmers.co.kr/learn/courses/30/lessons/144855
select category, sum(sales) as `TOTAL_SALES`
from book, book_sales
where book.book_id = book_sales.book_id
and year(sales_date) = 2022 and month(sales_date) = 1
group by category
order by category;
https://school.programmers.co.kr/learn/courses/30/lessons/151139
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
GROUP BY CAR_ID
HAVING COUNT(CAR_ID) >= 5
) AND (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC;