๐Ÿ“œ[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] ์กฐ๊ฑด๋ณ„๋กœ ๋ถ„๋ฅ˜ํ•˜์—ฌ ์ฃผ๋ฌธ์ƒํƒœ ์ถœ๋ ฅํ•˜๊ธฐ

Chobbyยท2023๋…„ 1์›” 2์ผ
1

SQL

๋ชฉ๋ก ๋ณด๊ธฐ
34/41

๐Ÿงก๋ฌธ์ œ ์„ค๋ช…

๋‹ค์Œ์€ ์‹ํ’ˆ๊ณต์žฅ์˜ ์ฃผ๋ฌธ์ •๋ณด๋ฅผ ๋‹ด์€ FOOD_ORDER ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. FOOD_ORDER ํ…Œ์ด๋ธ”์€ ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ ORDER_ID, PRODUCT_ID, AMOUNT, PRODUCE_DATE, IN_DATE,OUT_DATE,FACTORY_ID, WAREHOUSE_ID๋Š” ๊ฐ๊ฐ ์ฃผ๋ฌธ ID, ์ œํ’ˆ ID, ์ฃผ๋ฌธ์–‘, ์ƒ์‚ฐ์ผ์ž, ์ž…๊ณ ์ผ์ž, ์ถœ๊ณ ์ผ์ž, ๊ณต์žฅ ID, ์ฐฝ๊ณ  ID๋ฅผ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค.

Column nameTypeNullable
ORDER_IDVARCHAR(10)FALSE
PRODUCT_IDVARCHAR(5)FALSE
AMOUNTNUMBERFALSE
PRODUCE_DATEDATETRUE
IN_DATEDATETRUE
OUT_DATEDATETRUE
FACTORY_IDVARCHAR(10)FALSE
WAREHOUSE_IDVARCHAR(10)FALSE

๐Ÿ’›๋ฌธ์ œ

FOOD_ORDER ํ…Œ์ด๋ธ”์—์„œ 5์›” 1์ผ์„ ๊ธฐ์ค€์œผ๋กœ ์ฃผ๋ฌธ ID, ์ œํ’ˆ ID, ์ถœ๊ณ ์ผ์ž, ์ถœ๊ณ ์—ฌ๋ถ€๋ฅผ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ์ถœ๊ณ ์—ฌ๋ถ€๋Š” 5์›” 1์ผ๊นŒ์ง€ ์ถœ๊ณ ์™„๋ฃŒ๋กœ ์ด ํ›„ ๋‚ ์งœ๋Š” ์ถœ๊ณ  ๋Œ€๊ธฐ๋กœ ๋ฏธ์ •์ด๋ฉด ์ถœ๊ณ ๋ฏธ์ •์œผ๋กœ ์ถœ๋ ฅํ•ด์ฃผ์‹œ๊ณ , ๊ฒฐ๊ณผ๋Š” ์ฃผ๋ฌธ ID๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌํ•ด์ฃผ์„ธ์š”.


๐Ÿ’š์˜ˆ์‹œ

FOOD_ORDER ํ…Œ์ด๋ธ”์ด ๋‹ค์Œ๊ณผ ๊ฐ™์„ ๋•Œ

ORDER_IDPRODUCT_IDAMOUNTPRODUCE_DATEIN_DATEOUT_DATEFACTORY_IDWAREHOUSE_ID
OD00000051P000240002022-04-012022-04-212022-04-21FT19970003WH0005
OD00000052P000325002022-04-102022-04-272022-04-27FT19970003WH0006
OD00000053P000562002022-04-152022-04-302022-05-01FT19940003WH0003
OD00000054P000610002022-04-212022-04-30NULLFT19940003WH0009
OD00000055P000815002022-04-252022-05-112022-05-11FT19980003WH0009

SQL์„ ์‹คํ–‰ํ•˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ถœ๋ ฅ๋˜์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

ORDER_IDPRODUCT_IDOUT_DATE์ถœ๊ณ ์—ฌ๋ถ€
OD00000051P00022022-04-21์ถœ๊ณ ์™„๋ฃŒ
OD00000052P00032022-04-27์ถœ๊ณ ์™„๋ฃŒ
OD00000053P00052022-05-01์ถœ๊ณ ์™„๋ฃŒ
OD00000054P0006์ถœ๊ณ ๋ฏธ์ •
OD00000055P00082022-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
;
profile
๋‚ด ์ง€์‹์„ ๊ณต์œ ํ•  ์ˆ˜ ์žˆ๋Š” ๋Œ€๋‹ดํ•จ

0๊ฐœ์˜ ๋Œ“๊ธ€