[SQL] 카티션곱, 조인, 조건절

FROM used_goods_board b, used_goods_reply r

-> 두 테이블 카티션 곱 한 것, join 한 거랑은 다름

 

SELECT food_type, rest_id, rest_name, MAX(favorites) AS favorites
FROM rest_info
GROUP BY food_type
ORDER BY food_type DESC;

이렇게 하면 food_type, rest_id, rest_name은 제일 위의 값을 뽑고

favorites는 max 값을 뽑는다.

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
)
group by food_type
order by food_type desc

 

조건( start_date <= "2022-10-16" and end_date >= "2022-10-16" )을 만족하는 행이 하나라도 있으면 "대여중" 하나도 없으면 "대여 가능"

select 
car_id, 
case 
    when car_id in (
        select car_id 
        from car_rental_company_rental_history
        where start_date <= "2022-10-16" and end_date >= "2022-10-16"
    ) then "대여중"
    else "대여 가능"
end as availability
from car_rental_company_rental_history
group by car_id
order by car_id desc;

 

 

SELECT 
month(start_date) as month,
car_id, 
count(*) as records
from car_rental_company_rental_history
where car_id in (
    select car_id 
    from car_rental_company_rental_history
    where start_date >= "2022-08-01" and start_date < "2022-11-01"
    group by car_id
    having count(*) >= 5)
    and start_date >= "2022-08-01" and start_date < "2022-11-01"
group by car_id, month(start_date)
having count(*) <> 0
order by month, car_id desc
;

 

where 절에 and 이후의 start_date >= "2022-08-01" and start_date < "2022-11-01"이 왜 필요?

-> 이 조건이 없으면 뽑은 car_id의 다른 월의 기록을 가져올 수 있음 

예를 들어 car_id가 1인 차가 5월에 대여한 기록이 있다면 그 기록을 걸러줄 수 없다.