우선 제가 원하는건 TLMS_VIDEO_ROOM 테이블에서 이 컬럼이 VIDEO_CLASS_SCHDL_ID -3,-4,-5,-6인 애들중에
FRDT(등록일)이 가장 최근인것으로 1개씩 뽑아오고 싶습니다
그래서 여기저기 검색해가며 짠게 아래 쿼리인데
원하는대로 결과가 잘 안나와서 조언듣고자 같이 올립니다..ㅠㅠ
WITH VIDEO AS (
SELECT -3 AS VIDEO_CLASS_SCHDL_ID, '스쿨포유 회의실' AS VIDEO_CLASS_SCHDL_NM FROM DUAL UNION ALL
SELECT -4 AS VIDEO_CLASS_SCHDL_ID, '교사 회의실1' AS VIDEO_CLASS_SCHDL_NM FROM DUAL UNION ALL
SELECT -5 AS VIDEO_CLASS_SCHDL_ID, '교사 회의실2' AS VIDEO_CLASS_SCHDL_NM FROM DUAL UNION ALL
SELECT -6 AS VIDEO_CLASS_SCHDL_ID, '교사 회의실3' AS VIDEO_CLASS_SCHDL_NM FROM DUAL
)
SELECT ROWNUM, A.* FROM (
SELECT A.VIDEO_CLASS_SCHDL_ID, A.VIDEO_CLASS_SCHDL_NM,
CASE WHEN ROOM_CODE IS NULL OR ROOM_ENDDT IS NOT NULL THEN '-'
ELSE '회의 중'
END STATUS,
CASE WHEN ROOM_CODE IS NULL OR ROOM_ENDDT IS NOT NULL THEN '-'
ELSE (SELECT USR_NM FROM TCMN_USER_MASTER WHERE USRID = A.FRUSRID)
END FRUSRNM,
CASE WHEN ROOM_CODE IS NULL OR ROOM_ENDDT IS NOT NULL THEN '-'
ELSE TO_CHAR(TO_DATE(ROOM_STRTDT , 'YYYYMMDDHH24MISS'), 'YYYY/MM/DD HH24:MI')
END STRTDT,
ROOM_CODE
FROM (
SELECT A.*, ROOM_CODE, ROW_NUMBER() OVER(ORDER BY FRDT DESC) AS M, ROOM_STRTDT, ROOM_ENDDT, FRDT, FRUSRID FROM VIDEO A
LEFT OUTER JOIN TLMS_VIDEO_ROOM B
ON A.VIDEO_CLASS_SCHDL_ID = B.VIDEO_CLASS_SCHDL_ID
)A
WHERE A.M <=4
ORDER BY VIDEO_CLASS_SCHDL_ID DESC
WITH VIDEO AS (
SELECT -3 AS VIDEO_CLASS_SCHDL_ID, '스쿨포유 회의실' AS VIDEO_CLASS_SCHDL_NM FROM DUAL UNION ALL
SELECT -4 AS VIDEO_CLASS_SCHDL_ID, '교사 회의실1' AS VIDEO_CLASS_SCHDL_NM FROM DUAL UNION ALL
SELECT -5 AS VIDEO_CLASS_SCHDL_ID, '교사 회의실2' AS VIDEO_CLASS_SCHDL_NM FROM DUAL UNION ALL
SELECT -6 AS VIDEO_CLASS_SCHDL_ID, '교사 회의실3' AS VIDEO_CLASS_SCHDL_NM FROM DUAL
)
SELECT A.VIDEO_CLASS_SCHDL_ID, A.VIDEO_CLASS_SCHDL_NM,
CASE WHEN ROOM_CODE IS NULL OR ROOM_ENDDT IS NOT NULL THEN '-'
ELSE '회의 중'
END STATUS,
CASE WHEN ROOM_CODE IS NULL OR ROOM_ENDDT IS NOT NULL THEN '-'
ELSE (SELECT USR_NM FROM TCMN_USER_MASTER WHERE USRID = B.FRUSRID)
END FRUSRNM,
CASE WHEN ROOM_CODE IS NULL OR ROOM_ENDDT IS NOT NULL THEN '-'
ELSE TO_CHAR(TO_DATE(ROOM_STRTDT , 'YYYYMMDDHH24MISS'), 'YYYY/MM/DD HH24:MI')
END STRTDT,
ROOM_CODE
FROM VIDEO A
LEFT OUTER JOIN (
SELECT A.* FROM TLMS_VIDEO_ROOM A
INNER JOIN (
SELECT VIDEO_CLASS_SCHDL_ID, MAX(FRDT) AS FRDT FROM TLMS_VIDEO_ROOM
WHERE VIDEO_CLASS_SCHDL_ID IN (-3,-4,-5,-6)
GROUP BY VIDEO_CLASS_SCHDL_ID
)B
ON A.VIDEO_CLASS_SCHDL_ID = B.VIDEO_CLASS_SCHDL_ID AND A.FRDT = B.FRDT
)B
ON A.VIDEO_CLASS_SCHDL_ID = B.VIDEO_CLASS_SCHDL_ID
ORDER BY VIDEO_CLASS_SCHDL_ID DESC
;