꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 문의 드립니다..

by 깽즈 [SQL Query] [2014.04.02 11:24:40]

안녕하세요.
쿼리를 열심히 연습하고 있는 초보자 입니다..

다름이 아니라 쿼리를 뻥튀기해서 출력하는 문제 인데요..

SELECT INST_ID, USERNAME, STATUS, COUNT(1)

FROM GV$SESSION

WHERE 1=1

AND USERNAME IS NOT NULL

GROUP BY INST_ID, USERNAME, STATUS

ORDER BY 1,2,3;
===========================================================

INST_ID USERNAME STATUS COUNT(1)

1 HR INACTIVE 1

1 SYS ACTIVE 1

1 SYS INACTIVE 5

2 PUBLIC ACTIVE 2

2 PUBLIC INACTIVE 1

2 SYS ACTIVE 2

2 SYS INACTIVE 4

===========================================================

위의 결과에서 HR 계정의 상태가 현재 INACTIVE SESSION 1개가 출력되는데요

이거를 ACTIVE 상태인 SESSION이 0개인 것으로 함께 출력하고 싶은데요.

1 HR ACTIVE 0
1 HR INACTIVE 1

이렇게요...

어떻게 해야되는지 감이 잡히지가 않아서요..

도움 부탁드리겠습니다

감사합니다.
즐거운 하루 되세요.

by 필상 [2014.04.02 12:49:11]

WITH A AS(
SELECT 'HR' AS ID, 'INACTIVE' AS STATUS, 1 AS CNT FROM DUAL UNION ALL
SELECT 'SYS' AS ID, 'ACTIVE' AS STATUS, 1 AS CNT FROM DUAL UNION ALL
SELECT 'SYS' AS ID, 'INACTIVE' AS STATUS, 5 AS CNT FROM DUAL UNION ALL
SELECT 'PUBLIC' AS ID, 'INACTIVE' AS STATUS, 5 AS CNT FROM DUAL ),
B AS(
SELECT 'ACTIVE' AS STATUS FROM DUAL UNION ALL
SELECT 'INACTIVE' AS STATUS FROM DUAL )

SELECT A.ID, B.STATUS, NVL(A.CNT, 0)
FROM B LEFT OUTER JOIN A PARTITION BY (A.ID) ON (A.STATUS = B.STATUS)

by 김용학 [2014.04.02 13:41:12]