데이터베이스 질문과 답변
요구사항 : 특정 2명이 포함된 2명만 있는 방의 ROOM_ID 찾기 (단, 최근입력이 최신순, 없으면 방생성날짜)
특정 2명 : USER01, USER02
| ROOM_ID | USER_ID | UPD_DATETIME | INPUT_DATETIME | DEL_DATETIME | |||
| USER01|202007200802 | USER01 | 2020-07-20 | 8:11:23 | 2019-01-09 | 8:14:15 | ||
| USER01|202007200802 | USER02 | 2020-07-20 | 8:11:20 | 2020-07-20 | 7:58:26 | ||
| USER01|202007200802 | USER03 | 2020-07-20 | 8:11:20 | 2020-07-17 | 10:12:13 | 2020-07-20 | 7:53:36 |
| USER01|202007200801 | USER01 | 2020-07-20 | 8:11:20 | 2020-07-20 | 8:11:20 | 2020-07-20 | 7:53:34 |
| USER01|202007200801 | USER02 | 2020-07-20 | 8:11:20 | 2020-07-17 | 10:29:06 | 2020-07-20 | 7:53:32 |
| USER01|202007200905 | USER01 | 2020-07-20 | 8:10:42 | 2020-07-17 | 11:49:44 | ||
| USER01|202007200905 | USER02 | 2020-07-20 | 8:10:42 | 2020-07-17 | 16:29:14 | ||
| USER01|202007201005 | USER01 | 2020-07-20 | 8:10:39 | 2020-07-20 | 8:10:42 | ||
| USER01|202007201005 | USER02 | 2020-07-20 | 8:10:39 | 2020-07-17 | 14:44:03 | ||
| USER01|202007200909 | USER01 | 2020-07-20 | 8:10:38 | 2020-07-17 | 19:04:15 | ||
| USER01|202007200909 | USER03 | 2020-07-20 | 8:10:37 | 2020-07-17 | 19:04:14 | ||
| USER01|202007200909 | USER05 | 2020-07-20 | 8:10:37 | 2019-01-07 | 10:17:22 | ||
제가 짠 쿼리는 아래와 같습니다.
SELECT ROOM_ID
FROM (SELECT ROOM_ID
FROM MSS_CHATTING_ROOM_USER
WHERE ROOM_ID IN (SELECT ROOM_ID
FROM MSS_CHATTING_ROOM_USER
WHERE USER_ID = 'USER01'
AND DEL_DATETIME IS NULL
AND ROOM_ID IN (SELECT ROOM_ID
FROM MSS_CHATTING_ROOM_USER
WHERE USER_ID = 'USER02'
AND DEL_DATETIME IS NULL))
GROUP BY ROOM_ID
HAVING COUNT(*) = 2
ORDER BY NVL(MAX(UPD_DATETIME), MAX(INPUT_DATETIME)) DESC
)
WHERE ROWNUM <=1
;
결과 : USER01|202007200801
질문 : 지금 같은 테이블은 3번 조회하는데 이것을 줄일수 있는 방법이 있을까요?
SELECT *
FROM (SELECT room_id
FROM mss_chatting_room_user
WHERE del_datetime IS NULL
AND user_id IN ('USER01', 'USER02')
GROUP BY room_id
HAVING COUNT(*) = 2
ORDER BY NVL(MAX(upd_datetime), MAX(input_datetime)) DESC
)
WHERE ROWNUM <= 1
;
안녕하십니까 항상 많은 도움을 받고 있습니다.
우선 답변 감사합니다.
다름아니라 조건은 특정 2명이 포함된 2명만 있는 방이었는데
답변 주신 쿼리보면 IN이기 때문에 USER01 또는 USER02가 1명만 있어도
필터가 되고 있습니다.
SELECT *
FROM (SELECT room_id
FROM mss_chatting_room_user
WHERE del_datetime IS NULL
GROUP BY room_id
HAVING COUNT(*) = COUNT(CASE WHEN user_id IN ('USER01', 'USER02') THEN 1 END)
ORDER BY NVL(MAX(upd_datetime), MAX(input_datetime)) DESC
)
WHERE ROWNUM <= 1
;
주신 쿼리 확인해 봤습니다.
우선 이런 생각을 하신 마농님에게 존경심을 느끼며, 쿼리에 대한 무한한 가능성을 다시 한번 느꼈습니다
로직상 나에게 쓰기(자기 혼자있는방)이 가능성이 있어서
HAVING COUNT(*) = COUNT(CASE WHEN user_id IN ('USER01', 'USER02') THEN 1 END)
AND COUNT(*) <> 1
수정했습니다.
그리고 제가 변경한 쿼리는 아래와 같습니다.
SELECT RESULT_CU.ROOM_ID
FROM ( SELECT CU2.ROOM_ID
FROM mss_chatting_room_user CU2
WHERE CU2.USER_ID = 'USER01'
AND CU2.ROOM_ID IN ( SELECT MAX_CU.ROOM_ID
FROM ( SELECT CNT_CU.ROOM_ID
, CNT_CU.USER_ID
, MAX(CNT_CU.USER_CNT) OVER (PARTITION BY CNT_CU.ROOM_ID) AS MAX_CNT
, CNT_CU.DEL_DATETIME
, CNT_CU.INPUT_DATETIME
, CNT_CU.UPD_DATETIME
FROM ( SELECT CU1.ROOM_ID
, CU1.USER_ID
, ROW_NUMBER() OVER (PARTITION BY CU1.ROOM_ID ORDER BY CU1.USER_ID) USER_CNT
, CU1.DEL_DATETIME
, CU1.INPUT_DATETIME
, CU1.UPD_DATETIME
FROM mss_chatting_room_user CU1) CNT_CU) MAX_CU
WHERE MAX_CU.USER_ID = 'USER02'
AND MAX_CU.DEL_DATETIME IS NULL
GROUP BY MAX_CU.ROOM_ID
HAVING MAX(MAX_CU.MAX_CNT) = 2 )
ORDER BY NVL((CU2.UPD_DATETIME), (CU2.INPUT_DATETIME)) DESC
) RESULT_CU
WHERE ROWNUM<=1
;
쿼리를 잘 짤수 있는 공부방법이 있을까요?
) 버튼을 클릭하여 작성 하시면 됩니다.