데이터베이스 질문과 답변

빈 행 추가? 0 1 1,227

by ahriow [2017.01.25 12:34:15] 

 SELECT 1 rnum, '201012043' cd, '배수지' nm, '03' stime FROM DUAL
  UNION
  SELECT 2 rnum, '201012043' cd, '배수지' nm, '04' stime FROM DUAL 
  UNION 
  SELECT 1 rnum, '201011143' cd, '이지은' nm, '06' stime FROM DUAL 
  UNION
  SELECT 2 rnum, '201011143' cd, '이지은' nm, '07' stime FROM DUAL
  UNION
  SELECT 3 rnum, '201011143' cd, '이지은' nm, '08' stime FROM DUAL
  UNION
  SELECT 4 rnum, '201011143' cd, '이지은' nm, '09' stime FROM DUAL 
  UNION 
  SELECT 1 rnum, '201017043' cd, '아이린' nm, '06' stime FROM DUAL 
  UNION
  SELECT 2 rnum, '201017043' cd, '아이린' nm, '07' stime FROM DUAL 
  UNION
  SELECT 3 rnum, '201017043' cd, '아이린' nm, '08' stime FROM DUAL

위같은 데이터가 있습니다.

결과값이 rnum의 최고값을 찾아서 모든 모든 사람을 그 값만큼 넣고 stime은 null인 상태로 출력하고 싶습니다.

아래 돌려보시면 제가 원하는 결과가 나오는데요

해당 결과를 더 간단하게 뽑을 수 없을까요?

 

SELECT aa.rnum,aa.cd,aa.nm,bb.stime
FROM
	(
		SELECT *
		FROM 
		(	SELECT DISTINCT a.rnum 
			FROM 
			(
				SELECT 1 rnum, '201012043' cd, '배수지' nm, '03' stime FROM DUAL
				UNION
				SELECT 2 rnum, '201012043' cd, '배수지' nm, '04' stime FROM DUAL 
				UNION 
				SELECT 1 rnum, '201011143' cd, '이지은' nm, '06' stime FROM DUAL 
				UNION
				SELECT 2 rnum, '201011143' cd, '이지은' nm, '07' stime FROM DUAL
				UNION
				SELECT 3 rnum, '201011143' cd, '이지은' nm, '08' stime FROM DUAL
				UNION
				SELECT 4 rnum, '201011143' cd, '이지은' nm, '09' stime FROM DUAL 
				UNION 
				SELECT 1 rnum, '201017043' cd, '아이린' nm, '06' stime FROM DUAL 
				UNION
				SELECT 2 rnum, '201017043' cd, '아이린' nm, '07' stime FROM DUAL 
				UNION
				SELECT 3 rnum, '201017043' cd, '아이린' nm, '08' stime FROM DUAL 
			) a 
		) a 
		CROSS JOIN 
		(	SELECT DISTINCT a.cd, a.nm 
			FROM 
			(
				SELECT 1 rnum, '201012043' cd, '배수지' nm, '03' stime FROM DUAL
				UNION
				SELECT 2 rnum, '201012043' cd, '배수지' nm, '04' stime FROM DUAL 
				UNION 
				SELECT 1 rnum, '201011143' cd, '이지은' nm, '06' stime FROM DUAL 
				UNION
				SELECT 2 rnum, '201011143' cd, '이지은' nm, '07' stime FROM DUAL
				UNION
				SELECT 3 rnum, '201011143' cd, '이지은' nm, '08' stime FROM DUAL
				UNION
				SELECT 4 rnum, '201011143' cd, '이지은' nm, '09' stime FROM DUAL 
				UNION 
				SELECT 1 rnum, '201017043' cd, '아이린' nm, '06' stime FROM DUAL 
				UNION
				SELECT 2 rnum, '201017043' cd, '아이린' nm, '07' stime FROM DUAL 
				UNION
				SELECT 3 rnum, '201017043' cd, '아이린' nm, '08' stime FROM DUAL 
			) a 
		) b
	) aa
	LEFT JOIN 
	(
	  SELECT 1 rnum, '201012043' cd, '배수지' nm, '03' stime FROM DUAL
	  UNION
	  SELECT 2 rnum, '201012043' cd, '배수지' nm, '04' stime FROM DUAL 
	  UNION 
	  SELECT 1 rnum, '201011143' cd, '이지은' nm, '06' stime FROM DUAL 
	  UNION
	  SELECT 2 rnum, '201011143' cd, '이지은' nm, '07' stime FROM DUAL
	  UNION
	  SELECT 3 rnum, '201011143' cd, '이지은' nm, '08' stime FROM DUAL
	  UNION
	  SELECT 4 rnum, '201011143' cd, '이지은' nm, '09' stime FROM DUAL 
	  UNION 
	  SELECT 1 rnum, '201017043' cd, '아이린' nm, '06' stime FROM DUAL 
	  UNION
	  SELECT 2 rnum, '201017043' cd, '아이린' nm, '07' stime FROM DUAL 
	  UNION
	  SELECT 3 rnum, '201017043' cd, '아이린' nm, '08' stime FROM DUAL 
	) bb ON aa.rnum = bb.rnum AND aa.cd = bb.cd
order by aa.cd,aa.rnum

 

by jkson [2017.01.25 13:00:51] 
WITH T AS
(
SELECT 1 RNUM, '201012043' CD, '배수지' NM, '03' STIME FROM DUAL
UNION
SELECT 2 RNUM, '201012043' CD, '배수지' NM, '04' STIME FROM DUAL 
UNION
SELECT 1 RNUM, '201011143' CD, '이지은' NM, '06' STIME FROM DUAL 
UNION
SELECT 2 RNUM, '201011143' CD, '이지은' NM, '07' STIME FROM DUAL
UNION
SELECT 3 RNUM, '201011143' CD, '이지은' NM, '08' STIME FROM DUAL
UNION
SELECT 4 RNUM, '201011143' CD, '이지은' NM, '09' STIME FROM DUAL 
UNION
SELECT 1 RNUM, '201017043' CD, '아이린' NM, '06' STIME FROM DUAL 
UNION
SELECT 2 RNUM, '201017043' CD, '아이린' NM, '07' STIME FROM DUAL 
UNION
SELECT 3 RNUM, '201017043' CD, '아이린' NM, '08' STIME FROM DUAL
)
SELECT LV RNUM, CD, NM, STIME 
FROM (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(RNUM) FROM T)) A
LEFT OUTER JOIN T B
PARTITION BY (CD, NM)
ON A.LV = B.RNUM
ORDER BY 1,2, 3

 

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