๐Ÿ’พ DataBase

[MySQL] ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค - ๋ณดํ˜ธ์†Œ์—์„œ ์ค‘์„ฑํ™”ํ•œ ๋™๋ฌผ(Lv. 4)

mxnxeonx 2023. 5. 25. 11:03
728x90
728x90

[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] ๋ณดํ˜ธ์†Œ์—์„œ ์ค‘์„ฑํ™”ํ•œ ๋™๋ฌผ

๋ณดํ˜ธ์†Œ์—์„œ ์ค‘์„ฑํ™” ์ˆ˜์ˆ ์„ ๊ฑฐ์นœ ๋™๋ฌผ ์ •๋ณด๋ฅผ ์•Œ์•„๋ณด๋ ค ํ•ฉ๋‹ˆ๋‹ค. ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜ฌ ๋‹น์‹œ์—๋Š” ์ค‘์„ฑํ™”1๋˜์ง€ ์•Š์•˜์ง€๋งŒ, ๋ณดํ˜ธ์†Œ๋ฅผ ๋‚˜๊ฐˆ ๋‹น์‹œ์—๋Š” ์ค‘์„ฑํ™”๋œ ๋™๋ฌผ์˜ ์•„์ด๋””์™€ ์ƒ๋ฌผ ์ข…, ์ด๋ฆ„์„ ์กฐํšŒํ•˜๋Š” ์•„์ด๋”” ์ˆœ์œผ๋กœ ์กฐํšŒํ•˜๋Š” SQL ๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.

 

๋ฌธ์ œ ์„ค๋ช…

ANIMAL_INS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_INS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””, ์ƒ๋ฌผ ์ข…, ๋ณดํ˜ธ ์‹œ์ž‘์ผ, ๋ณดํ˜ธ ์‹œ์ž‘ ์‹œ ์ƒํƒœ, ์ด๋ฆ„, ์„ฑ๋ณ„ ๋ฐ ์ค‘์„ฑํ™” ์—ฌ๋ถ€๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

NAME TYPE NULLABLE
ANIMAL_ID VARCHAR(N) FALSE
ANIMAL_TYPE VARCHAR(N) FALSE
DATETIME DATETIME FALSE
INTAKE_CONDITION VARCHAR(N) FALSE
NAME VARCHAR(N) TRUE
SEX_UPON_INTAKE VARCHAR(N) FALSE

 

ANIMAL_OUTS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์—์„œ ์ž…์–‘ ๋ณด๋‚ธ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_OUTS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””, ์ƒ๋ฌผ ์ข…, ์ž…์–‘์ผ, ์ด๋ฆ„, ์„ฑ๋ณ„ ๋ฐ ์ค‘์„ฑํ™” ์—ฌ๋ถ€๋ฅผ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค. ANIMAL_OUTS ํ…Œ์ด๋ธ”์˜ ANIMAL_ID๋Š” ANIMAL_INS์˜ ANIMAL_ID์˜ ์™ธ๋ž˜ ํ‚ค์ž…๋‹ˆ๋‹ค.

NAME TYPE NULLABLE
ANIMAL_ID VARCHAR(N) FALSE
ANIMAL_TYPE VARCHAR(N) FALSE
DATETIME DATETIME FALSE
NAME VARCHAR(N) TRUE
SEX_UPON_OUTCOME VARCHAR(N) FALSE

 

์˜ˆ๋ฅผ ๋“ค์–ด, ANIMAL_INS ํ…Œ์ด๋ธ”๊ณผ ANIMAL_OUTS ํ…Œ์ด๋ธ”์ด ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค๋ฉด

 

ANIMAL_INS

ANIMAL_ID ANIMAL_TYPE DATETIME INTAKE_CONDITION NAME SEX_UPON_INTAKE
A367438 Dog 2015-09-10 16:01:00 Normal Cookie Spayed Female
A382192 Dog 2015-03-13 13:14:00 Normal Maxwell 2 Intact Male
A405494 Dog 2014-05-16 14:17:00 Normal Kaila Spayed Female
A410330 Dog 2016-09-11 14:09:00 Sick Chewy Intact Female

ANIMAL_OUTS

ANIMAL_ID ANIMAL_TYPE DATETIME NAME SEX_UPON_OUTCOME
A367438 Dog 2015-09-12 13:30:00 Cookie Spayed Female
A382192 Dog 2015-03-16 13:46:00 Maxwell 2 Neutered Male
A405494 Dog 2014-05-20 11:44:00 Kaila Spayed Female
A410330 Dog 2016-09-13 13:46:00 Chewy Spayed Female
  • Cookie๋Š” ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜ฌ ๋‹น์‹œ์— ์ด๋ฏธ ์ค‘์„ฑํ™”๋˜์–ด์žˆ์—ˆ์Šต๋‹ˆ๋‹ค.
  • Maxwell 2๋Š” ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ํ›„ ์ค‘์„ฑํ™”๋˜์—ˆ์Šต๋‹ˆ๋‹ค.
  • Kaila๋Š” ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜ฌ ๋‹น์‹œ์— ์ด๋ฏธ ์ค‘์„ฑํ™”๋˜์–ด์žˆ์—ˆ์Šต๋‹ˆ๋‹ค.
  • Chewy๋Š” ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ํ›„ ์ค‘์„ฑํ™”๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

 

โ€ป ์ค‘์„ฑํ™”๋ฅผ ๊ฑฐ์น˜์ง€ ์•Š์€ ๋™๋ฌผ์€ ์„ฑ๋ณ„ ๋ฐ ์ค‘์„ฑํ™” ์—ฌ๋ถ€์— Intact, ์ค‘์„ฑํ™”๋ฅผ ๊ฑฐ์นœ ๋™๋ฌผ์€ Spayed ๋˜๋Š” Neutered๋ผ๊ณ  ํ‘œ์‹œ๋˜์–ด์žˆ์Šต๋‹ˆ๋‹ค.

 


 

๋ฌธ์ œ ํ•ด์„ค

๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜ฌ ๋‹น์‹œ์—๋Š” ์ค‘์„ฑํ™”๋˜์ง€ ์•Š์•˜์ง€๋งŒ, ๋ณดํ˜ธ์†Œ๋ฅผ ๋‚˜๊ฐˆ ๋‹น์‹œ์—๋Š” ์ค‘์„ฑํ™”๋œ ๋™๋ฌผ์„ ์กฐํšŒํ•ด์•ผ ํ•œ๋‹ค.

์ฆ‰, ๋ณดํ˜ธ์†Œ์—์„œ ์ค‘์„ฑํ™”๋ฅผ ๊ฑฐ์นœ ๋™๋ฌผ์˜ ์ •๋ณด๋งŒ ์กฐํšŒํ•ด์•ผ ํ•˜๋ฏ€๋กœ ๋‘ ํ…Œ์ด๋ธ”์„ JOINํ•˜์—ฌ ๋ฌธ์ œ๋ฅผ ํ‘ผ๋‹ค.

  1. ANIMAL_INS, ANIMAL_OUTS ํ…Œ์ด๋ธ” ์กฐ์ธ
  2. ANIMAL_INS ํ…Œ์ด๋ธ”์˜ SEX_UPON_INTAKE ์ปฌ๋Ÿผ์€ Intact ๋ฌธ์ž์—ด์„ ํฌํ•จํ•ด์•ผ ํ•จ (์ค‘์„ฑํ™”X)
  3. ANIMAL_OUTS ํ…Œ์ด๋ธ”์˜ SEX_UPON_OUTCOME ์ปฌ๋Ÿผ์€ Spayed ๋˜๋Š” Neutered ๋ฌธ์ž์—ด์„ ํฌํ•จํ•ด์•ผ ํ•จ (์ค‘์„ฑํ™”O)
  4. ANIMAL_ID๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌ

JOIN, LIKE๋ฅผ ํ™œ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ๋Š”์ง€์— ๋Œ€ํ•œ ๋Šฅ๋ ฅ์„ ํ‰๊ฐ€ํ•˜๋Š” ๋ฌธ์ œ๋‹ค.

-- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š”
SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE LIKE '%Intact%'
    AND (OUTS.SEX_UPON_OUTCOME LIKE '%Spayed%' OR OUTS.SEX_UPON_OUTCOME LIKE '%Neutered%')
ORDER BY INS.ANIMAL_ID
728x90
320x100