Question

Mysql(if) , oracle (Decode , case) 정리

-- 가정문 문법
-- https://school.programmers.co.kr/learn/courses/30/lessons/164672?language=mysql

-- MySql
SELECT BOARDID,
WRITERID,
TITLE,
PRICE,
IF(STATUS = 'SALE', '판매중', IF(STATUS = 'RESERVED', '예약중', '거래완료')) AS STATUS
FROM USEDGOODSBOARD
WHERE DATEFORMAT(CREATEDDATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC

-- Oracle Decode
SELECT BOARDID,
WRITERID,
TITLE,
PRICE,
DECODE(STATUS, 'SALE', '판매중', 'RESERVED', '예약중', 'DONE', '거래완료') AS STATUS
FROM USEDGOODSBOARD
WHERE TOCHAR(CREATEDDATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID DESC

-- Oracle CASE - WHEN - ELSE - END
SELECT BOARDID,
WRITERID,
TITLE,
PRICE,
CASE
WHEN STATUS = 'SALE' THEN '판매중'
WHEN STATUS = 'RESERVED' THEN '예약중'
WHEN STATUS = 'DONE' THEN '거래완료'
ELSE '준비중'
END AS STATUS

FROM USEDGOODSBOARD
WHERE TOCHAR(CREATEDDATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID DESC

작성중인 코드―solution.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT BOARD_ID,
       WRITER_ID,
       TITLE,
       PRICE,
       CASE
           WHEN STATUS = 'SALE' THEN '판매중'
           WHEN STATUS = 'RESERVED' THEN '예약중'
           WHEN STATUS = 'DONE' THEN '거래완료'
           ELSE '준비중'
           END AS STATUS

FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID DESC

xmas6904201@gmail.com

2023.7.18 13:14

Mysql(if) , oracle (Decode , case) 정리

작성중인 코드―solution.sql

0 개의 답변

답변 쓰기

이 입력폼은 마크다운 문법을 지원합니다.