๐Ÿค”[ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค] ์—†์–ด์ง„ ๊ธฐ๋ก ์ฐพ๊ธฐ

Chobbyยท2022๋…„ 3์›” 21์ผ
0

SQL

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

๋ฌธ์ œ ์„ค๋ช…

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

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

์ฒœ์žฌ์ง€๋ณ€์œผ๋กœ ์ธํ•ด ์ผ๋ถ€ ๋ฐ์ดํ„ฐ๊ฐ€ ์œ ์‹ค๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ์ž…์–‘์„ ๊ฐ„ ๊ธฐ๋ก์€ ์žˆ๋Š”๋ฐ, ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๊ธฐ๋ก์ด ์—†๋Š” ๋™๋ฌผ์˜ ID์™€ ์ด๋ฆ„์„ ID ์ˆœ์œผ๋กœ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.

์˜ˆ์‹œ

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

ANIMAL_INS

ANIMAL_OUTS

ANIMAL_OUTS ํ…Œ์ด๋ธ”์—์„œ

  • Allie์˜ ID๋Š” ANIMAL_INS์— ์—†์œผ๋ฏ€๋กœ, Allie์˜ ๋ฐ์ดํ„ฐ๋Š” ์œ ์‹ค๋˜์—ˆ์Šต๋‹ˆ๋‹ค.
  • Gia์˜ ID๋Š” ANIMAL_INS์— ์žˆ์œผ๋ฏ€๋กœ, Gia์˜ ๋ฐ์ดํ„ฐ๋Š” ์œ ์‹ค๋˜์ง€ ์•Š์•˜์Šต๋‹ˆ๋‹ค.
  • Spice์˜ ID๋Š” ANIMAL_INS์— ์—†์œผ๋ฏ€๋กœ, Spice์˜ ๋ฐ์ดํ„ฐ๋Š” ์œ ์‹ค๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

๋”ฐ๋ผ์„œ SQL๋ฌธ์„ ์‹คํ–‰ํ•˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™์ด ๋‚˜์™€์•ผ ํ•ฉ๋‹ˆ๋‹ค.

ํ’€์ด ์‹œ์ž‘

-- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š”
SELECT OUTS.ANIMAL_ID, OUTS.NAME FROM ANIMAL_OUTS AS OUTS LEFT JOIN ANIMAL_INS AS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID WHERE INS.ANIMAL_ID IS NULL ORDER BY ANIMAL_ID;
profile
๋‚ด ์ง€์‹์„ ๊ณต์œ ํ•  ์ˆ˜ ์žˆ๋Š” ๋Œ€๋‹ดํ•จ

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