๋ค์์ ์ข
ํฉ๋ณ์์ ์ง๋ฃ ์์ฝ์ ๋ณด๋ฅผ ๋ด์ APPOINTMENT
ํ
์ด๋ธ ์
๋๋ค.
APPOINTMENT
ํ
์ด๋ธ์ ๋ค์๊ณผ ๊ฐ์ผ๋ฉฐ APNT_YMD
, APNT_NO
, PT_NO
, MCDP_CD
, MDDR_ID
, APNT_CNCL_YN
, APNT_CNCL_YMD
๋ ๊ฐ๊ฐ ์ง๋ฃ์์ฝ์ผ์, ์ง๋ฃ์์ฝ๋ฒํธ, ํ์๋ฒํธ, ์ง๋ฃ๊ณผ์ฝ๋, ์์ฌID, ์์ฝ์ทจ์์ฌ๋ถ, ์์ฝ์ทจ์๋ ์ง๋ฅผ ๋ํ๋
๋๋ค.
Column name | Type | Nullable |
---|---|---|
APNT_YMD | TIMESTAMP | FALSE |
APNT_NO | NUMBER(5) | FALSE |
PT_NO | VARCHAR(10) | FALSE |
MCDP_CD | VARCHAR(6) | FALSE |
MDDR_ID | VARCHAR(10) | FALSE |
APNT_CNCL_YN | VARCHAR(1) | TRUE |
APNT_CNCL_YMD | DATE | TRUE |
APPOINTMENT
ํ
์ด๋ธ์์ 2022๋
5์์ ์์ฝํ ํ์ ์๋ฅผ ์ง๋ฃ๊ณผ์ฝ๋ ๋ณ๋ก ์กฐํํ๋ SQL๋ฌธ์ ์์ฑํด์ฃผ์ธ์. ์ด๋, ์ปฌ๋ผ๋ช
์ '์ง๋ฃ๊ณผ ์ฝ๋', '5์์์ฝ๊ฑด์'๋ก ์ง์ ํด์ฃผ์๊ณ ๊ฒฐ๊ณผ๋ ์ง๋ฃ๊ณผ๋ณ ์์ฝํ ํ์ ์๋ฅผ ๊ธฐ์ค์ผ๋ก ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ๊ณ , ์์ฝํ ํ์ ์๊ฐ ๊ฐ๋ค๋ฉด ์ง๋ฃ๊ณผ ์ฝ๋๋ฅผ ๊ธฐ์ค์ผ๋ก ์ค๋ฆ์ฐจ์ ์ ๋ ฌํด์ฃผ์ธ์.
APPOINTMENT
ํ
์ด๋ธ์ด ๋ค์๊ณผ ๊ฐ์ ๋
APNT_YMD | APNT_NO | PT_NO | MCDP_CD | MDDR_ID | APNT_CNCL_YN | APNT_CNCL_YMD |
---|---|---|---|---|---|---|
2022-04-14 | 09:30:00.000000 | 47 | PT22000064 | GS | DR20170123 | N |
2022-04-15 | 10:30:00.000000 | 48 | PT22000065 | OB | DR20100231 | N |
2022-05-15 | 17:30:00.000000 | 49 | PT22000086 | OB | DR20100231 | N |
2022-05-18 | 10:30:00.000000 | 52 | PT22000019 | GS | DR20100039 | N |
2022-05-19 | 12:00:00.000000 | 53 | PT22000020 | FM | DR20010112 | N |
2022-05-22 | 08:30:00.000000 | 54 | PT22000021 | GS | DR20100039 | N |
2022-05-04 | 10:30:00.000000 | 56 | PT22000023 | FM | DR20090112 | N |
2022-05-14 | 15:30:00.000000 | 57 | PT22000074 | CS | DR20200012 | N |
2022-05-24 | 15:30:00.000000 | 58 | PT22000085 | CS | DR20200012 | N |
2022-05-28 | 10:00:00.000000 | 60 | PT22000092 | OS | DR20100031 | N |
SQL์ ์คํํ๋ฉด ๋ค์๊ณผ ๊ฐ์ด ์ถ๋ ฅ๋์ด์ผ ํฉ๋๋ค.
์ง๋ฃ๊ณผ์ฝ๋ | 5์์์ฝ๊ฑด์ |
---|---|
OB | 1 |
OS | 1 |
CS | 2 |
FM | 2 |
GS | 2 |
SELECT
MCDP_CD AS ์ง๋ฃ๊ณผ์ฝ๋,
COUNT(*) AS "5์์์ฝ๊ฑด์"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD, 'mm') = '05'
GROUP BY MCDP_CD
ORDER BY "5์์์ฝ๊ฑด์", MCDP_CD