🐬 MySQL/λ¬Έμ œν’€μ΄

[MySQL] SQL μ½”λ”©ν…ŒμŠ€νŠΈ Day11

xod22 2022. 2. 23. 19:24
728x90

https://programmers.co.kr/learn/courses/30/lessons/59045

 

μ½”λ”©ν…ŒμŠ€νŠΈ μ—°μŠ΅ - λ³΄ν˜Έμ†Œμ—μ„œ μ€‘μ„±ν™”ν•œ 동물

ANIMAL_INS ν…Œμ΄λΈ”μ€ 동물 λ³΄ν˜Έμ†Œμ— λ“€μ–΄μ˜¨ λ™λ¬Όμ˜ 정보λ₯Ό 담은 ν…Œμ΄λΈ”μž…λ‹ˆλ‹€. ANIMAL_INS ν…Œμ΄λΈ” κ΅¬μ‘°λŠ” λ‹€μŒκ³Ό κ°™μœΌλ©°, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKEλŠ” 각각 λ™λ¬Όμ˜ 아이디

programmers.co.kr

문제 (λ³΄ν˜Έμ†Œμ—μ„œ 쀑성화 ν•œ 동물) : 

 

λ³΄ν˜Έμ†Œμ—μ„œ 쀑성화 μˆ˜μˆ μ„ 거친 동물 정보λ₯Ό μ•Œμ•„λ³΄λ € ν•©λ‹ˆλ‹€. λ³΄ν˜Έμ†Œμ— λ“€μ–΄μ˜¬ λ‹Ήμ‹œμ—λŠ” μ€‘μ„±ν™”λ˜μ§€ μ•Šμ•˜μ§€λ§Œ, λ³΄ν˜Έμ†Œλ₯Ό λ‚˜κ°ˆ λ‹Ήμ‹œμ—λŠ” μ€‘μ„±ν™”λœ λ™λ¬Όμ˜ 아이디와 생물 μ’…, 이름을 μ‘°νšŒν•˜λŠ” 아이디 순으둜 μ‘°νšŒν•˜λŠ” SQL 문을 μž‘μ„±ν•΄μ£Όμ„Έμš”.

 

풀이

 

방법1)

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID=OUTS.ANIMAL_ID
AND INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME
ORDER BY INS.ANIMAL_ID;
  •  SEX_UPON_INTAKE와 SEX_UPON_OUTCOME이 λ‹€λ₯΄λ‹€λ©΄ 쀑성화λ₯Ό ν–ˆλ‹€λŠ” 것!

쀑성화->μžμ—° (λΆˆκ°€)

μžμ—°->쀑성화 (κ°€λŠ₯)

ν•˜κΈ°λ•Œλ¬Έμ— 이 방법을 μ“Έ 수 있음!

=> WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME

 

 

방법2)

--쀑성화 Neutered, Spayed μžμ—°μ˜ Intact
SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID=OUTS.ANIMAL_ID
AND INS.SEX_UPON_INTAKE LIKE "Intact%" 
AND (OUTS.SEX_UPON_OUTCOME LIKE "Neutered%" OR OUTS.SEX_UPON_OUTCOME LIKE "Spayed%")
ORDER BY INS.ANIMAL_ID;
  • SEX_UPON_INTAKEμ—μ„œ Intack(μžμ—°)이닀가 SEX_UPON_OUTCOMEμ—μ„œ Neutered/Spayed(쀑성화)인 κ²ƒλ§Œ 뽑아냄!

=> WHERE INS.SEX_UPON_INTAKE LIKE "Intact%" AND (OUTS.SEX_UPON_OUTCOME LIKE "Neutered%" OR OUTS.SEX_UPON_OUTCOME LIKE "Spayed%")

728x90