[SQL] WITH ์ ˆ

๊ฐœ-๋ฐœ๋šœ-๋ฐœยท2023๋…„ 7์›” 28์ผ
0

์ฟผ๋ฆฌ

๋ชฉ๋ก ๋ณด๊ธฐ
4/7

WITH

๐ŸŒžWITH ์ ˆ

์˜ค๋ผํด9i R2 ๋ฒ„์ „๋ถ€ํ„ฐ ์‚ฌ์šฉ๊ฐ€๋Šฅ

์„œ๋ธŒ์ฟผ๋ฆฌ์™€ ๋˜‘๊ฐ™์€ ๊ฐœ๋…์ด๋‹ค.

์ž„์‹œํ…Œ์ด๋ธ”์„ ํ˜น์€ ๊ฐ€์ƒํ…Œ์ด๋ธ”์„ ๋งŒ๋“œ๋Š” ๊ฒƒ์ด๋‹ค.

๋ฐ˜๋ณต์ ์œผ๋กœ SQL๋ฌธ์„ ์‚ฌ์šฉํ•ด์•ผ ํ•  ๊ฒฝ์šฐ, ์žฌ์‚ฌ์šฉ ํ•  ์ˆ˜ ์žˆ๋‹ค.

ํ•œ๋ฒˆ์˜ WITH์ ˆ๋กœ ์—ฌ๋Ÿฌ ์ฟผ๋ฆฌ๋ฅผ ๋งŒ๋“ค์ง€ ์•Š์•„์„œ ์„ฑ๋Šฅ ํ–ฅ์ƒ์— ๋„์›€์ด๋œ๋‹ค.

โ›…์‚ฌ์šฉ๋ฒ•

WITH EX AS
	(
	   SELECT 'WITH' AS WI1
		FROM DUAL
	)
	SELECT *
	 FROM EX

โ†’ WI1์˜ ์ด๋ฆ„์„ ๊ฐ€์ง„ ์ปฌ๋Ÿผ์— โ€˜WITHโ€™์ด ์ถœ๋ ฅ๋จ.

WI1
WITH

โ›…๋‹ค์ค‘ WITH์ ˆ

๊ธฐ์กด WITH์ ˆ์—์„œ ์ฝค๋งˆ(,)๋กœ ๊ตฌ๋ถ„ํ•˜์—ฌ ์—ฌ๋Ÿฌ๊ฐœ ์„ ์–ธํ•œ๋‹ค.

WITH EX_1 AS
( 
  SELECT name AS ์ด๋ฆ„
    FROM emp
   GROUP BY name
), 
EX_2 AS 
(
  SELECT job
       , AVG(money) AS ํ‰๊ท ์›”๊ธ‰
    FROM emp
   GROUP BY job
)

์ฝค๋งˆ(,)๋กœ ๊ตฌ๋ถ„ํ•˜์—ฌ ๋‹ค์ค‘WITH์„ ์„ ์–ธํ•œ๋‹ค.

profile
๊ด€์‹ฌO ๋Œ“๊ธ€O ๊ฐ๋†”๋ผ๋ฐฐ๋†”๋ผO ๊ฐ€๋ฅด์ณ์ฃผ๋Š”๊ฑฐO ํ•œ๊ฐ€ํ• ๋•Œ์˜ฌ๋ฆฝ๋‹ˆ๋‹ค

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