나만의 개발블로그

-- 날짜간 비교는 DATEDIFF 잊지 말기! SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, IF (DATEDIFF(END_DATE,START_DATE) >= 30, '장기 대여', '단기 대여') AS RENT_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE LIKE '2022-09%' ORDER BY HISTORY_ID DESC

SELECT T2.FLAVOR FROM FIRST_HALF T1, JULY T2 WHERE T1.FLAVOR = T2.FLAVOR GROUP BY T2.FLAVOR ORDER BY SUM(T2.TOTAL_ORDER)+T1.TOTAL_ORDER DESC LIMIT 3

-- CONCAT으로 문자 합쳐서 원하는 대로 출력하기 SELECT CONCAT('/home/grep/src/', T2.BOARD_ID, '/', T2.FILE_ID, T2.FILE_NAME, T2.FILE_EXT) AS FILE_PATH FROM USED_GOODS_BOARD T1 JOIN USED_GOODS_FILE T2 ON T1.BOARD_ID = T2.BOARD_ID WHERE T1.VIEWS = (SELECT MAX(VIEWS) AS VIEWS FROM USED_GOODS_BOARD LIMIT 1 ) ORDER BY T2.FILE_ID DESC

SELECT NAME, DATETIME FROM ANIMAL_INS WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS ) ORDER BY 2 LIMIT 3

SELECT T1.PRODUCT_CODE, SUM(T1.PRICE*T2.SALES_AMOUNT) AS SALES FROM PRODUCT T1 JOIN OFFLINE_SALE T2 ON T1.PRODUCT_ID = T2.PRODUCT_ID GROUP BY T1.PRODUCT_CODE ORDER BY 2 DESC, 1

--REST_REVIEW 테이블에서 MEMBER_ID 를 기준으로 그룹핑 한 다음 -- 개수를 기준으로 정렬한 다음 LIMIT으로 1개만 조회하도록 하기 SELECT T1.MEMBER_NAME, T2.REVIEW_TEXT, DATE_FORMAT(T2.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE FROM MEMBER_PROFILE T1 JOIN REST_REVIEW T2 ON T1.MEMBER_ID = T2.MEMBER_ID WHERE T1.MEMBER_ID = (SELECT MEMBER_ID FROM REST_REVIEW GROUP BY MEMBER_ID ORDER BY COUNT(MEMBER_ID) DESC LIMIT 1 ) ORDER BY 3, 2

SELECT T1.BOOK_ID, T2.AUTHOR_NAME, DATE_FORMAT(T1.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK T1 JOIN AUTHOR T2 ON T1.AUTHOR_ID = T2.AUTHOR_ID WHERE T1.CATEGORY = '경제' ORDER BY 3

-- SELECT 절에 삼항연산을 사용해 출력문 제어 -- 그리고 DATE 타입의 경우 날짜 비교시 -- 굳이 문자로 나타낸 날짜에 DATE_FORMAT를 해주지 않아도 비교가 가능 SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE, IF (OUT_DATE IS NULL,'출고미정', IF (OUT_DATE > '2022-05-01', '출고대기', '출고완료')) AS 출고여부 FROM FOOD_ORDER ORDER BY 1 -- CASE 사용으로 출력문 제어 SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE, CASE WHEN OUT_..

-- 의사와 진료 테이블의 컬럼을 보고 엮어줄게 없어서 헤매다가 -- 다른 이름의 컬럼으로 같은 값을 확인해 해결 SELECT T3.APNT_NO, T1.PT_NAME, T1.PT_NO, T3.MCDP_CD, T2.DR_NAME, T3.APNT_YMD FROM PATIENT T1, DOCTOR T2, APPOINTMENT T3 WHERE T1.PT_NO = T3.PT_NO AND T2.DR_ID = T3.MDDR_ID AND T3.APNT_CNCL_YN = 'N' AND T3.MCDP_CD = 'CS' AND T3.APNT_YMD LIKE '2022-04-13%' ORDER BY T3.APNT_YMD

-- CASE를 사용한 출력문 제어 -- 그리고 CASE를 사용하면 컬럼명이 CASE에 적힌대로 나오지 않기에 ALIAS 사용하기 SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE STATUS WHEN 'SALE' THEN '판매중' WHEN 'RESERVED' THEN '예약중' ELSE '거래완료' END AS STATUS FROM USED_GOODS_BOARD WHERE CREATED_DATE = '2022-10-05' ORDER BY BOARD_ID DESC