순위? 매기는 쿼리를 짜다가 막혀서 질문드립니당. 0 3 784

by zumsim [2020.06.08 12:45:54]


우선 제가 원하는건 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

 

 

by zumsim [2020.06.08 13:45:02]

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
;


by 마농 [2020.06.08 14:05:51]

1. 수정전
  ROW_NUMBER() OVER(ORDER BY frdt DESC) m
  WHERE a.m <= 4
2. 수정후
  ROW_NUMBER() OVER(PARTITION BY a.video_class_schdl_id ORDER BY b.frdt DESC) m
  WHERE a.m <= 1


by zumsim [2020.06.08 14:20:40]

그 뒤에 ROW_NUMBER() OVER(PARTITION BY a.video_class_schdl_id ORDER BY b.frdt DESC) m 여기까지도 수정했는데 정상출력안되서 다른방법 찾고 있었는데 WHERE 조건쪽도 바꿨어야하는군요..ㅠㅠ 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입