๐งก๋ฌธ์ ์ค๋ช
๋ค์์ ์ํ๊ณต์ฅ์ ์ฃผ๋ฌธ์ ๋ณด๋ฅผ ๋ด์ FOOD_ORDER
ํ
์ด๋ธ์
๋๋ค. FOOD_ORDER
ํ
์ด๋ธ์ ๋ค์๊ณผ ๊ฐ์ผ๋ฉฐ ORDER_ID
, PRODUCT_ID
, AMOUNT
, PRODUCE_DATE
, IN_DATE
,OUT_DATE
,FACTORY_ID
, WAREHOUSE_ID
๋ ๊ฐ๊ฐ ์ฃผ๋ฌธ ID, ์ ํ ID, ์ฃผ๋ฌธ์, ์์ฐ์ผ์, ์
๊ณ ์ผ์, ์ถ๊ณ ์ผ์, ๊ณต์ฅ ID, ์ฐฝ๊ณ ID๋ฅผ ์๋ฏธํฉ๋๋ค.
Column name | Type | Nullable |
---|---|---|
ORDER_ID | VARCHAR(10) | FALSE |
PRODUCT_ID | VARCHAR(5) | FALSE |
AMOUNT | NUMBER | FALSE |
PRODUCE_DATE | DATE | TRUE |
IN_DATE | DATE | TRUE |
OUT_DATE | DATE | TRUE |
FACTORY_ID | VARCHAR(10) | FALSE |
WAREHOUSE_ID | VARCHAR(10) | FALSE |
๐๋ฌธ์
FOOD_ORDER
ํ
์ด๋ธ์์ 5์ 1์ผ์ ๊ธฐ์ค์ผ๋ก ์ฃผ๋ฌธ ID, ์ ํ ID, ์ถ๊ณ ์ผ์, ์ถ๊ณ ์ฌ๋ถ๋ฅผ ์กฐํํ๋ SQL๋ฌธ์ ์์ฑํด์ฃผ์ธ์. ์ถ๊ณ ์ฌ๋ถ๋ 5์ 1์ผ๊น์ง ์ถ๊ณ ์๋ฃ๋ก ์ด ํ ๋ ์ง๋ ์ถ๊ณ ๋๊ธฐ๋ก ๋ฏธ์ ์ด๋ฉด ์ถ๊ณ ๋ฏธ์ ์ผ๋ก ์ถ๋ ฅํด์ฃผ์๊ณ , ๊ฒฐ๊ณผ๋ ์ฃผ๋ฌธ ID๋ฅผ ๊ธฐ์ค์ผ๋ก ์ค๋ฆ์ฐจ์ ์ ๋ ฌํด์ฃผ์ธ์.
๐์์
FOOD_ORDER
ํ
์ด๋ธ์ด ๋ค์๊ณผ ๊ฐ์ ๋
ORDER_ID | PRODUCT_ID | AMOUNT | PRODUCE_DATE | IN_DATE | OUT_DATE | FACTORY_ID | WAREHOUSE_ID |
---|---|---|---|---|---|---|---|
OD00000051 | P0002 | 4000 | 2022-04-01 | 2022-04-21 | 2022-04-21 | FT19970003 | WH0005 |
OD00000052 | P0003 | 2500 | 2022-04-10 | 2022-04-27 | 2022-04-27 | FT19970003 | WH0006 |
OD00000053 | P0005 | 6200 | 2022-04-15 | 2022-04-30 | 2022-05-01 | FT19940003 | WH0003 |
OD00000054 | P0006 | 1000 | 2022-04-21 | 2022-04-30 | NULL | FT19940003 | WH0009 |
OD00000055 | P0008 | 1500 | 2022-04-25 | 2022-05-11 | 2022-05-11 | FT19980003 | WH0009 |
SQL์ ์คํํ๋ฉด ๋ค์๊ณผ ๊ฐ์ด ์ถ๋ ฅ๋์ด์ผ ํฉ๋๋ค.
ORDER_ID | PRODUCT_ID | OUT_DATE | ์ถ๊ณ ์ฌ๋ถ |
---|---|---|---|
OD00000051 | P0002 | 2022-04-21 | ์ถ๊ณ ์๋ฃ |
OD00000052 | P0003 | 2022-04-27 | ์ถ๊ณ ์๋ฃ |
OD00000053 | P0005 | 2022-05-01 | ์ถ๊ณ ์๋ฃ |
OD00000054 | P0006 | ์ถ๊ณ ๋ฏธ์ | |
OD00000055 | P0008 | 2022-05-11 | ์ถ๊ณ ๋๊ธฐ |
๐๋์ ํ์ด
SELECT
ORDER_ID,
PRODUCT_ID,
TO_CHAR(OUT_DATE, 'YYYY-MM-DD') AS OUT_DATE,
CASE
WHEN TO_NUMBER(TO_CHAR(OUT_DATE,'YYYYMMDD')) <= '20220501' THEN '์ถ๊ณ ์๋ฃ'
WHEN TO_NUMBER(TO_CHAR(OUT_DATE,'YYYYMMDD')) > '20220501' THEN '์ถ๊ณ ๋๊ธฐ'
ELSE '์ถ๊ณ ๋ฏธ์ '
END AS "์ถ๊ณ ์ฌ๋ถ"
FROM FOOD_ORDER
ORDER BY ORDER_ID
;