나만의 개발블로그

-- SUBSTRING 메서드를 활용해 문자열 슬라이싱을 할 수 있으며 -- CONCAT 시 다른 요소와 구분해야 한다면 꼭 ' '를 추가해 빈칸을 만들어 주기! SELECT T2.USER_ID, T2.NICKNAME, CONCAT(T2.CITY, ' ', T2.STREET_ADDRESS1, ' ', T2.STREET_ADDRESS2) AS 전체주소, CONCAT(LEFT(T2.TLNO, 3), '-', substring(T2.TLNO, 4, 4), '-', SUBSTRING(T2.TLNO, 8, 4)) AS 전화번호 FROM USED_GOODS_BOARD T1 JOIN USED_GOODS_USER T2 ON T1.WRITER_ID = T2.USER_ID GROUP BY T1.WRITER_ID HAVIN..

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%네비게이션%' ORDER BY 1 DESC

-- 날짜간 비교는 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_..