[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 301650 ํŠน์ • ์„ธ๋Œ€์˜ ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ (์ž๊ธฐ ์ฐธ์กฐ, ์„œ๋ธŒ ์ฟผ๋ฆฌ)

2025. 2. 17. 18:51ยท๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜/SQL

 

๋ฌธ์ œ

https://school.programmers.co.kr/learn/courses/30/lessons/301650

 

ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค

SW๊ฐœ๋ฐœ์ž๋ฅผ ์œ„ํ•œ ํ‰๊ฐ€, ๊ต์œก, ์ฑ„์šฉ๊นŒ์ง€ Total Solution์„ ์ œ๊ณตํ•˜๋Š” ๊ฐœ๋ฐœ์ž ์„ฑ์žฅ์„ ์œ„ํ•œ ๋ฒ ์ด์Šค์บ ํ”„

programmers.co.kr

 

3์„ธ๋Œ€์˜ ๋Œ€์žฅ๊ท ์˜ ID(ID) ๋ฅผ ์ถœ๋ ฅํ•˜๋Š” SQL ๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ๋Œ€์žฅ๊ท ์˜ ID ์— ๋Œ€ํ•ด ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌํ•ด์ฃผ์„ธ์š”.

 

๋ฌธ์ œ ํ’€์ด์— ์•ž์„œ..

์ž๊ธฐ ์ฐธ์กฐ ๊ด€๊ณ„

: ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์ด ์•„๋‹Œ ์ž๊ธฐ ์ž์‹ ๊ณผ์˜ ๊ด€๊ณ„๋ฅผ ๋งบ๋Š” ํƒ€์ž…์œผ๋กœ ์ˆœํ™˜ ๊ด€๊ณ„๋ผ๊ณ ๋„ ํ•œ๋‹ค.

 

์œ„ ๋ฌธ์ œ์˜ ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋ฅผ ์‚ดํŽด๋ณด์ž.

์œ„ ํ…Œ์ด๋ธ”์˜ parent_id์™€ id๋ฅผ ์กฐ๊ฑด์œผ๋กœ ํ•˜์—ฌ ์ž๊ธฐ์ฐธ์กฐ๋ฅผ ํ•˜๋Š” ๊ตฌ๋ฌธ์€ ์•„๋ž˜์™€ ๊ฐ™์ด ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

select child.id as child_id , parent.id as parent_id
from ecoli_data child join (select id from ecoli_data) parent
on child.parent_id = parent.id

 

์‹คํ–‰ ๊ฒฐ๊ณผ๋Š” ์•„๋ž˜์™€ ๊ฐ™๋‹ค.

์ด์ฒ˜๋Ÿผ ์ž๊ธฐ ์ž์‹ ๊ณผ์˜ ๊ด€๊ณ„๋ฅผ ๋งบ๋Š” ๊ฒƒ์„ "์ž๊ธฐ ์ฐธ์กฐ ๊ด€๊ณ„" ๋ผ ํ•˜๋ฉฐ, ์ฃผ๋กœ ๊ณ„์ธต ๊ด€๊ณ„๋ฅผ ํ‘œํ˜„ํ•  ๋•Œ ์‚ฌ์šฉํ•œ๋‹ค.

(ex. ์ง์› - ๊ด€๋ฆฌ์ž, ๋ถ€๋ชจ - ์ž์‹) 

 

 

 

๋ฌธ์ œ ํ’€์ด 1 - ์ž๊ธฐ ์ฐธ์กฐ

์•ž์„  ์ž๊ธฐ ์ฐธ๊ณ  ๊ด€๊ณ„๋ฅผ ์ด์šฉํ•˜์—ฌ 2์ค‘ JOIN๋ฌธ์„ ํ†ตํ•ด 3์„ธ๋Œ€์˜ ID๋ฅผ ๊ตฌํ•œ๋‹ค.

 

1. 2์„ธ๋Œ€ ๊ตฌํ•˜๊ธฐ

SELECT child.id AS gen2, parent.id AS gen1 
FROM ecoli_data child 
JOIN (SELECT * FROM ecoli_data) parent
ON child.parent_id = parent.id
WHERE parent.parent_id IS NULL

๋ถ€๋ชจ๊ฐ€ NULL์ธ ๋ถ€๋ชจ(=1์„ธ๋Œ€)๋ฅผ ๊ฐ€์ง„ ์ž๋…€๋ฅผ ๊ตฌํ•œ๋‹ค.

 

 

2. 3์„ธ๋Œ€ ๊ตฌํ•˜๊ธฐ

SELECT child.id AS ID
FROM ecoli_data child 
JOIN (
    SELECT child.id AS gen2, parent.id AS gen1 
    FROM ecoli_data child 
    JOIN (SELECT * FROM ecoli_data) parent
    ON child.parent_id = parent.id
    WHERE parent.parent_id IS NULL
) past
ON child.parent_id = past.gen2
ORDER BY child.id;

1์—์„œ ๊ตฌํ•œ 2์„ธ๋Œ€๋ฅผ ๋ถ€๋ชจ๋กœ ํ•˜๋Š” ์ž๋…€๋ฅผ ๊ตฌํ•œ๋‹ค.

 

 

 

 

๋ฌธ์ œ ํ’€์ด 2 - ์„œ๋ธŒ ์ฟผ๋ฆฌ

๊ฐ™์€ ๋กœ์ง์„ ์„œ๋ธŒ ์ฟผ๋ฆฌ๋กœ ๊ตฌํ˜„ํ•˜์˜€๋‹ค.

์ž๊ธฐ ์ฐธ์กฐ ๋Œ€์‹  WHERE - IN ์กฐ๊ฑด ์ ˆ์„ ์‚ฌ์šฉํ•˜์˜€๋‹ค.

 

1. 2์„ธ๋Œ€ ๊ตฌํ•˜๊ธฐ

# 2์„ธ๋Œ€
SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (
	# 1์„ธ๋Œ€
    SELECT ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
)

๋ถ€๋ชจ๊ฐ€ 1์„ธ๋Œ€์ธ ์ž๋…€(=2์„ธ๋Œ€)๋ฅผ ๊ตฌํ•œ๋‹ค.

 

 

2. 3์„ธ๋Œ€ ๊ตฌํ•˜๊ธฐ

# 3์„ธ๋Œ€
SELECT ID 
FROM ECOLI_DATA
WHERE PARENT_ID IN (
	# 2์„ธ๋Œ€
    SELECT ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IN (
    	# 1์„ธ๋Œ€
        SELECT ID
        FROM ECOLI_DATA
        WHERE PARENT_ID IS NULL
        
    )
)
ORDER BY ID;

1์—์„œ ๊ตฌํ•œ 2์„ธ๋Œ€๋ฅผ ๋ถ€๋ชจ๋กœ ํ•˜๋Š” ์ž๋…€(3์„ธ๋Œ€)๋ฅผ ๊ตฌํ•œ๋‹ค.

 

 

 

 

์ด ๋ฐฉ๋ฒ•์ด ๋งž๋Š”์ง€์— ๋Œ€ํ•œ ์˜๋ฌธ์€ ๊ฐ€์กŒ์ง€๋งŒ LEVEL 4 ์น˜๊ณ ๋Š” ์ƒ๊ฐ๋ณด๋‹ค ์‰ฌ์šด ๋ฌธ์ œ์˜€๋‹ค .!! 

'๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜ > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299307/301649 ๋Œ€์žฅ๊ท ์˜ ํฌ๊ธฐ์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜ํ•˜๊ธฐ 1, 2 (CASE WHEN , WINDOW ํ•จ์ˆ˜)  (8) 2025.02.10
[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299310 ์—ฐ๋„๋ณ„ ๋Œ€์žฅ๊ท  ํฌ๊ธฐ์˜ ํŽธ์ฐจ ๊ตฌํ•˜๊ธฐ (์„œ๋ธŒ์ฟผ๋ฆฌ, Window ํ•จ์ˆ˜)  (0) 2025.02.07
[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 298519 ํŠน์ • ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋ฌผ๊ณ ๊ธฐ๋ณ„ ์ˆ˜์™€ ์ตœ๋Œ€ ๊ธธ์ด ๊ตฌํ•˜๊ธฐ - SQL ๊ณ ๋“์  Kit  (4) 2025.02.06
'๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜/SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€
  • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299307/301649 ๋Œ€์žฅ๊ท ์˜ ํฌ๊ธฐ์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜ํ•˜๊ธฐ 1, 2 (CASE WHEN , WINDOW ํ•จ์ˆ˜)
  • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 299310 ์—ฐ๋„๋ณ„ ๋Œ€์žฅ๊ท  ํฌ๊ธฐ์˜ ํŽธ์ฐจ ๊ตฌํ•˜๊ธฐ (์„œ๋ธŒ์ฟผ๋ฆฌ, Window ํ•จ์ˆ˜)
  • [ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 298519 ํŠน์ • ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋ฌผ๊ณ ๊ธฐ๋ณ„ ์ˆ˜์™€ ์ตœ๋Œ€ ๊ธธ์ด ๊ตฌํ•˜๊ธฐ - SQL ๊ณ ๋“์  Kit
.๋ฐ.
.๋ฐ.
  • .๋ฐ.
    Do IT
    .๋ฐ.
  • ์ „์ฒด
    ์˜ค๋Š˜
    ์–ด์ œ
    • All (40)
      • ๐Ÿ’ป ์•Œ๊ณ ๋ฆฌ์ฆ˜ (21)
        • PS (16)
        • SQL (4)
        • ์ด๋ก  (5)
      • ๐ŸŽˆcapstone (2)
      • ๐Ÿ’ชBackend (12)
        • Django (8)
        • Spring (4)
  • ๋ธ”๋กœ๊ทธ ๋ฉ”๋‰ด

    • ํ™ˆ
    • ํƒœ๊ทธ
    • ๋ฐฉ๋ช…๋ก
  • ๋งํฌ

  • ๊ณต์ง€์‚ฌํ•ญ

  • ์ธ๊ธฐ ๊ธ€

  • ํƒœ๊ทธ

    ์Šค์ผ€์ค„๋Ÿฌ
    ์žฌ๊ท€
    PS
    MYSQL
    ์„œ๋ธŒ์ฟผ๋ฆฌ
    BOJ
    ํŒŒ์ด์ฌ
    ์ฝ”ํ…Œ
    Batch
    responsecustomclass
    ์•Œ๊ณ ๋ฆฌ์ฆ˜
    apiresponse
    springscheduler
    windowํ•จ์ˆ˜
    resposneentity
    ์‘๋‹ตํ˜•์‹
    ๋ฌธ์ œํ’€์ด
    crud
    ์ž๋ฐ”
    ETL
    bruteforce
    SQL
    programmers
    BFS
    ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค
    python
    Django
    ๋ฐฑ์ค€
    ์Šคํ”„๋ง๋ฐฐ์น˜
    ๋‹ค์ค‘์กฐ์ธ
  • ์ตœ๊ทผ ๋Œ“๊ธ€

  • ์ตœ๊ทผ ๊ธ€

  • hELLOยท Designed By์ •์ƒ์šฐ.v4.10.3
.๋ฐ.
[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค/PRGMS] 301650 ํŠน์ • ์„ธ๋Œ€์˜ ๋Œ€์žฅ๊ท  ์ฐพ๊ธฐ (์ž๊ธฐ ์ฐธ์กฐ, ์„œ๋ธŒ ์ฟผ๋ฆฌ)
์ƒ๋‹จ์œผ๋กœ

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”