WHERE, ORDER BY(3)
조건문을 만들 때 사용한다.
💨 조건에 부합하는 중고거래 상태 조회하기
CASE WHEN을 이용하면 되는 간단한 문제
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE WHEN STATUS='DONE' THEN '거래완료'
WHEN STATUS='SALE' THEN '판매중'
WHEN STATUS='RESERVED' THEN '예약중'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE,"%Y-%m-%d")='2022-10-05'
ORDER BY BOARD_ID DESC;🦊 오랜 기간 보호한 동물(1)
서브쿼리를 이용, ANIMAL_OUTS에 없는 동물이 입양을 못갔으므로 해당 ANIMAL_ID를 제외한 행들을 출력해주면 된다.
LIMIT를 이용해 위부터 3개만 출력
SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3;JOIN을 이용한 풀이도 가능
SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A
LEFT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.DATETIME IS NULL
ORDER BY A.DATETIME
LIMIT 3;🚕 대여 기록이 존재하는 자동차 리스트 구하기
WHERE문으로 CAR_RENTAL_COMPANY_CAR 테이블에서 차 종류가 '세단' 인 것을 선택- 서브쿼리로 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 10월에 대여한 기록이 있는 것을 선택하고
- 두개를 만족하는 CAR_ID를 찾아 내림차순으로 정렬
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '세단' AND CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE MONTH(START_DATE)='10')
ORDER BY CAR_ID DESC;📂 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
CONCAT을 이용해 문자열을 연결
서브쿼리를 이용해 USED_GOODS_BOARD 테이블에 있는 행들을 VIEWS로 정렬한 뒤, 제일 큰 조회수 VIEWS의 BOARD_ID를 받아 출력
SELECT CONCAT('/home/grep/src/', BOARD_ID,'/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (SELECT BOARD_ID
FROM USED_GOODS_BOARD
ORDER BY VIEWS DESC
LIMIT 1)
ORDER BY FILE_ID DESC;📊 조건별로 분류하여 주문상태 출력하기
CASE WHEN을 사용해 조건을 분류해준다.
AS를 사용해 코드를 좀 더 간소화해서 가독성을 높여보자!
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,"%Y-%m-%d") AS OUT_DATE,
CASE WHEN DATE_FORMAT(OUT_DATE,"%Y-%m-%d") > '2022-05-01' THEN '출고대기'
WHEN DATE_FORMAT(OUT_DATE,"%Y-%m-%d") <= '2022-05-01' THEN '출고완료'
ELSE "출고미정" END AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID;
//같은 코드
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,"%Y-%m-%d") AS OUT_DATE,
CASE WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
ELSE "출고미정" END AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID;🐶 보호소에서 중성화한 동물
ANIMAL_INS 테이블에서 Infact로 시작하는 행을 찾고, WHERE문을 이용해 ANIMAL_OUTS 테이블에서 중성화하지 않은 ANIMAL_ID를 찾아 조건을 달아주면 된다.
이때, 조건에 맞는 행이 여러개 일 수 있으므로 =이 아닌 IN을 이용한다.
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE "Intact%"
AND ANIMAL_ID IN (SELECT ANIMAL_ID
FROM ANIMAL_OUTS
WHERE SEX_UPON_OUTCOME LIKE "Spayed%"
OR SEX_UPON_OUTCOME LIKE "Neutered%")
ORDER BY ANIMAL_ID🚘 자동차 대여 기록 별 대여 금액 구하기
CASE WHEN을 사용해서 대여 기록별 할인율을 곱해주면 된다.
SELECT A.HISTORY_ID,
ROUND((DATEDIFF(A.END_DATE, A.START_DATE)+1)* (CASE
WHEN DATEDIFF(A.END_DATE, A.START_DATE)+1 < 7 THEN 1
WHEN DATEDIFF(A.END_DATE, A.START_DATE)+1 < 30 THEN 0.95
WHEN DATEDIFF(A.END_DATE, A.START_DATE)+1 < 90 THEN 0.92
ELSE 0.85 END)* B.DAILY_FEE, 0) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS A
JOIN CAR_RENTAL_COMPANY_CAR AS B
ON A.CAR_ID = B.CAR_ID
WHERE B.CAR_TYPE = "트럭"
ORDER BY FEE DESC, A.HISTORY_ID DESC💰 오프라인/온라인 판매 데이터 통합하기
UNION을 사용해 두 테이블을 합집합으로 연결하면 된다.
이때 컬럼 이름이 다르지 않도록 유의!
UNION : 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거하고 정렬을 발생시킨다. (UNION은 UNION DISTINCT와 동일한 작업을 한다)
UNION ALL : 중복을 제거하거나 정렬을 유발하지 않는다.
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE,"%Y-%m")='2022-03'
UNION
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE,"%Y-%m")='2022-03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID🍝 그룹별 조건에 맞는 식당 목록 출력하기
두 테이블을 JOIN을 이용해 연결해주고, WHERE절에 서브쿼리를 이용해 리뷰를 제일 많이 작성한 회원 한명만 리턴해준다.
GROUP BY를 사용해서 MEMBER_ID 별로 묶어주고,ORDER BY에서 COUNT를 이용해 MEMBER_ID 카운트한 내림차순으로 정렬하여 상위 1개를 리턴해주면 된다.
SELECT M.MEMBER_NAME, R.REVIEW_TEXT,
DATE_FORMAT(REVIEW_DATE,"%Y-%m-%d") AS REVIEW_DATE
FROM MEMBER_PROFILE AS M
JOIN REST_REVIEW AS R
ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = (SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(MEMBER_ID) DESC LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT🪅 오랜 기간 보호한 동물(2)
ANIMAL_OUTS에는 존재하는데, ANIMAL_INS에는 기록이 없는 동물을 제외하기 위해 WHERE문을 사용해 제외해주면 되고,
보호 기간이 제일 길었던 순서부터 정렬해주면 되기 때문에 O.DATETIME 과 I.DATETIME의 차의 역순으로 정렬해 출력해주면 된다.
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS AS O
LEFT JOIN ANIMAL_INS AS I
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NOT NULL
ORDER BY O.DATETIME-I.DATETIME DESC
LIMIT 2