๐™ณ๐™ฐ๐šƒ๐™ฐ๐™ฑ๐™ฐ๐š‚๐™ด

[SQL] ์ข‹์€ ์ฟผ๋ฆฌ ์ž‘์„ฑ๋ฒ•

ํ•ด๋ฒ„๋‹ˆ 2024. 4. 29. 16:07
๋ฐ˜์‘ํ˜•

 

 

 

[SQL] LEFT JOIN์„ ํ™œ์šฉํ•œ ๋ˆ„๋ฝ๋œ ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌํ•˜๊ธฐ — dovnaldisn (tistory.com)

 

[SQL] LEFT JOIN์„ ํ™œ์šฉํ•œ ๋ˆ„๋ฝ๋œ ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌํ•˜๊ธฐ

์œ„ ํ‘œ๋ฅผ ๋ณด๋ฉด codetype์— fruit์ด ์žˆ๊ณ  codekey์—๋Š” ๊ณผ์ผ์˜ ์ข…๋ฅ˜๊ฐ€ ์ ํ˜€์žˆ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ๊ฐ๊ฐ ๊ณผ์ผ์— ๋Œ€ํ•œ ์ข…๋ฅ˜์™€ ์ด๋ฆ„์ด ๋‚˜์™€์žˆ๋‹ค. ํ˜„์žฌ ํฌ๋„๋Š” 4๊ฐœ, ์‚ฌ๊ณผ๋Š” 2๊ฐœ, ์˜ค๋ Œ์ง€๋Š” 3๊ฐœ, ๋ฐฐ๋Š” 0๊ฐœ์ธ๋ฐ ํฌ๋„, ์‚ฌ๊ณผ,

dovnaldisn.tistory.com

์ด ๊ธ€์˜ ์ฝ”๋“œ ๋ฆฌ๋ทฐ๋ฅผ ๋ฐ›๊ณ  ์žฌ์ž‘์„ฑ์„ ํ•ด๋ณธ๋‹ค. 

 

 

 

 

 

์˜ˆ์ œ ํ…Œ์ด๋ธ” ๊ตฌ์กฐ

์˜ˆ์‹œ ํ…Œ์ด๋ธ” ์†์„ฑ์€ ์ด๋ ‡๊ฒŒ ๋œ๋‹ค.

 

 

 

insert into fruit (codetype, codekey, koname)
values 
	('apple','green apple','์•„์˜ค๋ฆฌ ์‚ฌ๊ณผ'),
	('apple','red apple','์‚ฌ๊ณผ'),
	('fruit','grape','ํฌ๋„'),
	('fruit','apple','์‚ฌ๊ณผ'),
	('fruit','orrange','์˜ค๋ Œ์ง€'),
	('fruit','pear','๋ฐฐ'),
	('grape','kyoho','๊ฑฐ๋ด‰'),
	('grape','shine muscat','์ƒค์ธ๋จธ์Šค์บฃ'),
	('grape','black shpphire','๋ธ”๋ž™์‚ฌํŒŒ์ด์–ด'),
	('grape','green grape','์ฒญํฌ๋„'),
	('orrange','tangerine','ํ…์ €๋ฆฐ'),
	('orrange','navel orrange','๋„ค์ด๋ธ” ์˜ค๋ Œ์ง€'),
	('orrange','valencia orrange','๋ฐœ๋ Œ์‹œ์•„ ์˜ค๋ Œ์ง€');

๋ฐ์ดํ„ฐ๋Š” ์ด๋ ‡๊ฒŒ insert ํ–ˆ๋‹ค. 

 

 

 

 

 

 

 

๊ณผ์ผ ์ข…๋ฅ˜๋ณ„๋กœ countํ•˜๋Š” ์ฟผ๋ฆฌ๋ฌธ์„ ์งฐ๋‹ค. 

 

 

 

 

 

 

 

 

 

๋ฌธ์ œ (์ฝ”๋“œ ๊ฐœ์„ )

ํ•ญ๋ชฉ์— ์—†๋Š” ๊ฑด ์ œ์™ธํ•˜๊ณ  count ํ•˜๊ธฐ

 

์›๋ž˜ ์ฝ”๋“œ

select B.codekey, count(A.codekey)
from (select * from fruit where codetype like 'fruit') B
join fruit A
on A.codetype = B.codekey
group by B.codekey;

 

โœ… ๊ฐœ์„ ๋œ ์ฝ”๋“œ

	select min(B.codekey), 
		count(*) as "ํ•ญ๋ชฉ์ˆ˜"
	from fruit B, 
		fruit A
	where 1=1
			and A.codetype = B.codekey
group by B.codekey;

 

 

 

 

 

 

ํ•ญ๋ชฉ์— ์—†๋Š” ๊ฒƒ๋„ count ํ•˜๊ธฐ

์›๋ž˜ ์ฝ”๋“œ

select B.codekey, count(A.codekey)
from (select * from fruit where codetype like 'fruit') B
left join fruit A
on A.codetype = B.codekey
group by B.codekey;

 

 

 

โœ… ๊ฐœ์„ ๋œ ์ฝ”๋“œ

	select min(A.codekey),
		count(B.*) as "ํ•ญ๋ชฉ์ˆ˜"
	from fruit A
	left join fruit B
		on A.codekey = B.codetype
	where 1=1
		and A.codetype = 'fruit'
group by A.codekey;

 

 

 

 

 

๊ฐœ์„  ๋ฐฉ์•ˆ

1๏ธโƒฃ ๋“ค์—ฌ์“ฐ๊ธฐ ์ž˜ ํ•ด์ฃผ๊ธฐ

์›๋ž˜ ์ฝ”๋“œ์ฒ˜๋Ÿผ ๋“ค์—ฌ์“ฐ๊ธฐ๋ฅผ ํ•˜์ง€ ์•Š๋Š”๋‹ค๋ฉด ๊ฐ€๋…์„ฑ์ด ๋–จ์–ด์ง„๋‹ค.

(์ฝ”๋“œ์˜ ๊ธธ์ด๊ฐ€ ๊ธธ์–ด์ง€๋ฉด ๊ฐ€๋…์„ฑ์ด ์—„์ฒญ ๋–จ์–ด์ง) 

์‚ฌ๋žŒ๋งˆ๋‹ค ์Šคํƒ€์ผ์ด ๋‹ค ๋‹ค๋ฅด๋ฏ€๋กœ ์ฐธ๊ณ ํ•ด์„œ ์—ฐ์Šตํ•˜๋Š”๊ฒŒ ์ข‹๋‹ค. 

 

 

 

2๏ธโƒฃ ๊ฐ€๋กœ๋กœ ๊ธธ์–ด์ง€์ง€ ์•Š๊ฒŒ ์ ์ ˆํžˆ Enter ํ•ด์ฃผ๊ธฐ 

์ฝ”๋“œ ์ค„์ด ๊ธด ๊ฑด ์ƒ๊ด€ ์—†์ง€๋งŒ, ๊ฐ€๋กœ๋กœ ๊ธธ์–ด์ง€๋ฉด ๋ณด๊ธฐ ํž˜๋“ค์–ด์ง€๊ธฐ ๋•Œ๋ฌธ์— ๊ฐ€๋กœ๋กœ ๊ธธ์–ด์ง€๊ธฐ ์ „์— ์ค„๋ฐ”๊ฟˆ์„ ํ•ด์ฃผ๋Š” ๊ฒŒ ์ข‹๋‹ค. 

 

	SELECT A.column1,
    	   A.column2,
    	   B.column3,
    	   MAX(B.column4) AS max_value
	FROM 
    table1 A
LEFT JOIN table2 B 
	ON A.column1 = B.column1
GROUP BY 
    A.column1,
    A.column2,
    B.column3;

์ด๋Ÿฐ ์‹์œผ๋กœ Enter์™€ Tab์„ ์ ์ ˆํžˆ ์‚ฌ์šฉํ•ด์•ผ ํ•œ๋‹ค. 

 

 

 

 

3๏ธโƒฃ ์„œ๋ธŒ์ฟผ๋ฆฌ ์‚ฌ์šฉ ์ž์ œ

์›๋ž˜ ์ฝ”๋“œ์—๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์„œ๋ธŒ ์ฟผ๋ฆฌ๊ฐ€ ์žˆ๋‹ค.

from (select * from fruit where codetype like 'fruit') B

 

ํ•˜์ง€๋งŒ ์„œ๋ธŒ ์ฟผ๋ฆฌ๋Š” ์ตœ!๋Œ€!ํ•œ! ์‚ฌ์šฉ ์ž์ œํ•˜๋Š” ๊ฒŒ ์ข‹๋‹ค.

๋ฐ์ดํ„ฐ ์–‘์ด ๋ฐฉ๋Œ€ํ•ด์งˆ์ˆ˜๋ก ์†๋„๊ฐ€ ๋Š๋ ค์ง„๋‹ค.

→ from ์ ˆ์—๋Š” ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ํ•จ๊ป˜ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค. 

 

 

 

 

4๏ธโƒฃ group by๋Š” ํ†ตํ•ฉํ•  ๋Œ€์ƒ๋งŒ ์ ์–ด์ฃผ๋Š” ๊ฒŒ ์ข‹๋‹ค.

๊ทธ๋Ÿผ ๋‚˜๋จธ์ง€๋Š” ์–ด๋–ป๊ฒŒ ์ ์–ด?

→ min()์ด๋‚˜ max()๋กœ ํ‘œ์‹œํ•˜๋ฉด ๋œ๋‹ค.  

 

 

 

 

5๏ธโƒฃ Where ์ ˆ์— 1=1 ์ ์–ด์ฃผ๊ธฐ 

	SELECT * 
	FROM employees
	WHERE 1=1
	-- AND name = 'John'
	-- AND age = 23
    	-- ์ฃผ์„ ๋„ฃ๊ธฐ๋„ ํŽธํ•˜๊ณ , ๋ชจ๋“  ์กฐ๊ฑด์„ ์ฃผ์„์„ ๋‹ฌ์•„๋„ ํ•ญ์ƒ ์ฐธ์ด๋‹ค.
order by num;

 

์ฟผ๋ฆฌ๋ฌธ์„ ์ž‘์„ฑํ•  ๋•Œ, 1=1์„ ๋„ฃ์–ด์ฃผ๋ฉด ๋ชจ๋“  ์กฐ๊ฑด์„ ์ฃผ์„์ฒ˜๋ฆฌํ•ด๋„ ์˜ค๋ฅ˜๊ฐ€ ๋‚˜์ง€ ์•Š๋Š”๋‹ค.

์ด๋Š” WHERE ์ ˆ์— 1=1์ด๋ผ๋Š” ์กฐ๊ฑด์ด ์žˆ์œผ๋ฉด, ์ด ์กฐ๊ฑด์ด ํ•ญ์ƒ ์ฐธ(TRUE)์ด๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.

์ฒ˜์Œ WHERE์— 1=1๋ฅผ ์ ์–ด์ฃผ๊ณ  ๋‹ค์Œ ์กฐ๊ฑด๋ถ€ํ„ฐ 'AND ์กฐ๊ฑด์‹'์œผ๋กœ ์ž‘์„ฑํ•˜๋ฉด ํ†ต์ผ์„ฑ๋„ ์žˆ๊ณ  ์ฃผ์„๋„ ๋„ฃ๊ธฐ ํŽธํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์ด๋Ÿฐ ์‹์œผ๋กœ ์ž‘์„ฑํ•˜๋Š” ๊ฒƒ์„ ๊ถŒ์žฅํ•œ๋‹ค.

 

 

๋ฐ˜์‘ํ˜•