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

심심할것 같아 하나 재밌는거 올려봅니다. 여기에 있을수도 있지만 ^^ 어려운 문제는 아닙니다.

by 미카엘 [2013.06.24 11:20:46]

테스트 데이터

with tmp1 as (

select '1' s_id, 'kang' s_nm from dual union all

select '2' s_id, 'kimkukjin' s_nm from dual union all

select '3' s_id, 'kimkildong' s_nm from dual union all

select '4' s_id, 'seojinsu' s_nm from dual),

tmp2 as (

select '1' c_id, 'DataBase' c_nm from dual union all

select '2' c_id, 'Java' c_nm from dual),

tmp3 as (

select '1' s_id, '1' c_id , 1 chasu from dual union all

select '1' s_id, '1' c_id , 3 chasu from dual union all

select '1' s_id, '2' c_id , 2 chasu from dual union all

select '2' s_id, '1' c_id , 1 chasu from dual union all

select '2' s_id, '1' c_id , 2 chasu from dual union all

select '2' s_id, '1' c_id , 3 chasu from dual union all

select '3' s_id, '2' c_id , 1 chasu from dual union all

select '3' s_id, '2' c_id , 2 chasu from dual union all

select '4' s_id, '1' c_id , 1 chasu from dual)

결과집합

아이디	성명	스터디	1차	2차	3차	참여횟수
1	강호동	Database	O		O	2
		Java		O		1
2	유재석	Database	O	O	O	3
		Java				0
3	김제동	Database				0
		Java	O	O		2
4	김국진	Database	O			1
		Java				0

좋은 하루 보내세요. ^^

by 손님 [2013.06.24 11:25:39]

정답은 여기에.. ^^ http://www.gurubee.net/lecture/2190

by 미카엘 [2013.06.24 11:33:21]

정말 있네요. 전 다른 블로그에서 찾은건뎅. ㅎㅎ

by 손님 [2013.06.25 11:14:08]

아우터조인 배워봅니다~~~

with t1 as (

select '1' s_id, 'kang' s_nm from dual union all

select '2' s_id, 'kimkukjin' s_nm from dual union all

select '3' s_id, 'kimkildong' s_nm from dual union all

select '4' s_id, 'seojinsu' s_nm from dual),

t2 as (

select '1' c_id, 'DataBase' c_nm from dual union all

select '2' c_id, 'Java' c_nm from dual),

t3 as (

select '1' s_id, '1' c_id , 1 chasu from dual union all

select '1' s_id, '1' c_id , 3 chasu from dual union all

select '1' s_id, '2' c_id , 2 chasu from dual union all

select '2' s_id, '1' c_id , 1 chasu from dual union all

select '2' s_id, '1' c_id , 2 chasu from dual union all

select '2' s_id, '1' c_id , 3 chasu from dual union all

select '3' s_id, '2' c_id , 1 chasu from dual union all

select '3' s_id, '2' c_id , 2 chasu from dual union all

select '4' s_id, '1' c_id , 1 chasu from dual)

select T1.S_ID as "아이디",DECODE(S_NM,'kang','강호동','kimkukjin','유재석','kimkildong','김제동','seojinsu','김국진') as "성명"

,T1.C_NM as "스터디"

,min(DECODE(CHASU,1,'O',null)) as "1차"

,min(DECODE(CHASU,2,'O',null)) AS "2차"

,min(DECODE(CHASU,3,'O',null)) as "3차"

,count(t3.s_id) AS "참여횟수"

from (select t1.s_id, t1.s_nm,t2.c_id,t2.c_nm from t1,t2) T1,T3

where T1.S_ID=T3.S_ID(+)

and T1.C_ID=T3.C_ID(+)

group by T1.S_ID,T1.S_NM,T1.C_ID,T1.C_NM

order by t1.s_id;

by 윤 [2013.07.11 15:24:35]

with t1 as (

select '1' s_id, 'kang' s_nm from dual union all

select '2' s_id, 'kimkukjin' s_nm from dual union all

select '3' s_id, 'kimkildong' s_nm from dual union all

select '4' s_id, 'seojinsu' s_nm from dual),

t2 as (

select '1' C_ID, 'DataBase' C_NM from DUAL union all

select '2' C_ID, 'Java' C_NM from DUAL)

,t3 as (

select '1' s_id, '1' c_id , 1 chasu from dual union all

select '1' s_id, '1' c_id , 3 chasu from dual union all

select '1' s_id, '2' c_id , 2 chasu from dual union all

select '2' s_id, '1' c_id , 1 chasu from dual union all

select '2' s_id, '1' c_id , 2 chasu from dual union all

select '2' s_id, '1' c_id , 3 chasu from dual union all

select '3' s_id, '2' c_id , 1 chasu from dual union all

select '3' S_ID, '2' C_ID , 2 CHASU from DUAL union all

select '4' S_ID, '1' C_ID , 1 CHASU from DUAL)

select NULLIF(T1.S_ID, LAG(T1.S_ID) over(order by T1.S_ID,T1.C_NM)) "아이디"

,DECODE(NULLIF(T1.S_NM, LAG(T1.S_NM) over(order by T1.S_ID,T1.C_NM))

,'kang','강호동','kimkukjin','유재석','kimkildong','김제동','seojinsu','김국진') "성명"

,T1.C_NM "스터디"

,min(case when T3.CHASU = 1 then 'ㅇ' end) "1차"

,min(case when T3.CHASU = 2 then 'ㅇ' end) "2차"

,min(case when T3.CHASU = 3 then 'ㅇ' end) "3차"

,COUNT(T3.CHASU)"참여횟수"

from(select * from T1,T2

order by S_ID,C_NM)T1, T3

where T1.S_ID=T3.S_ID(+)

and T1.C_ID = T3.C_ID(+)

group by T1.S_ID, T1.S_NM, T1.C_NM

order by T1.S_ID, T1.C_NM

;

중복내용 처리와 case 문을 사용하여....
아우터조인 배워봅니다!!

by terry0515 [2016.01.20 13:37:36]

with tmp1 as (
select '1' s_id, 'kang' s_nm union all
select '2' s_id, 'kimkukjin' s_nm union all
select '3' s_id, 'kimkildong' s_nm union all
select '4' s_id, 'seojinsu' s_nm ),
tmp2 as (
select '1' c_id, 'DataBase' c_nm union all
select '2' c_id, 'Java' c_nm ),
tmp3 as (
select '1' s_id, '1' c_id , 1 chasu union all
select '1' s_id, '1' c_id , 3 chasu union all
select '1' s_id, '2' c_id , 2 chasu union all
select '2' s_id, '1' c_id , 1 chasu union all
select '2' s_id, '1' c_id , 2 chasu union all
select '2' s_id, '1' c_id , 3 chasu union all
select '3' s_id, '2' c_id , 1 chasu union all
select '3' s_id, '2' c_id , 2 chasu union all
select '4' s_id, '1' c_id , 1 chasu )

select
      (Case When ROW_NUMBER() over (PARTITION by a.s_id Order By a.s_id,b.c_nm) = 1
               Then a.s_id
            Else ''
       End
      )        As s_id
      ,max(a.s_nm)
      ,b.c_nm
      ,IsNull(Min(Case When c.chasu = 1 Then 'O' End),'')
      ,IsNull(Min(Case When c.chasu = 2 Then 'O' End),'')
      ,IsNull(Min(Case When c.chasu = 3 Then 'O' End),'')
      , IsNull(Min(Case When c.chasu = 1 Then 1 End),0)
       + IsNull(Min(Case When c.chasu = 2 Then 1 End),0)
       + IsNull(Min(Case When c.chasu = 3 Then 1 End),0)
from tmp1 a
              Full Outer Join tmp2 b On 1 = 1
              Left Outer Join tmp3 c
                                     On a.s_id = c.s_id
                                    And b.c_id = c.c_id
group by a.s_id
        ,b.c_nm
order by a.s_id
        ,b.c_nm

by 앙악 [2017.06.21 13:56:51]

with tmp1 as (
select '1' s_id, 'kang' s_nm from dual union all
select '2' s_id, 'kimkukjin' s_nm from dual union all
select '3' s_id, 'kimkildong' s_nm from dual union all
select '4' s_id, 'seojinsu' s_nm from dual),
tmp2 as (
select '1' c_id, 'DataBase' c_nm from dual union all
select '2' c_id, 'Java' c_nm from dual),
tmp3 as (
select '1' s_id, '1' c_id , 1 chasu from dual union all
select '1' s_id, '1' c_id , 3 chasu from dual union all
select '1' s_id, '2' c_id , 2 chasu from dual union all
select '2' s_id, '1' c_id , 1 chasu from dual union all
select '2' s_id, '1' c_id , 2 chasu from dual union all
select '2' s_id, '1' c_id , 3 chasu from dual union all
select '3' s_id, '2' c_id , 1 chasu from dual union all
select '3' s_id, '2' c_id , 2 chasu from dual union all
select '4' s_id, '1' c_id , 1 chasu from dual),
FINAL AS
(
SELECT *
FROM tmp1
, tmp2
WHERE 1=1
ORDER BY 1, 2
)
SELECT NULLIF(FINAL.S_ID, LAG(FINAL.S_ID) over(order by FINAL.S_ID,FINAL.C_NM)) AS S_ID
, NULLIF(MAX(S_NM), LAG(MAX(S_NM)) over(order by FINAL.S_ID,FINAL.C_NM)) AS S_NM
, C_NM
, LISTAGG(CASE WHEN CHASU = 1 THEN 'O' ELSE NULL END) WITHIN GROUP(ORDER BY 1) AS "1차"
, LISTAGG(CASE WHEN CHASU = 2 THEN 'O' ELSE NULL END) WITHIN GROUP(ORDER BY 1) AS "2차"
, LISTAGG(CASE WHEN CHASU = 3 THEN 'O' ELSE NULL END) WITHIN GROUP(ORDER BY 1) AS "3차"
, COUNT(TMP3.S_ID) AS "참여횟수"
-- ,
FROM FINAL
, TMP3
WHERE 1=1
AND TMP3.S_ID (+)= FINAL.S_ID
AND TMP3.C_ID (+)= FINAL.C_ID
GROUP BY FINAL.S_ID, C_NM
;

이 쿼리의 문제점을 혹시 아시나요 ?/////////////////////////////// ㅠㅠㅠ

by 박군two [2017.06.30 10:45:54]

재밌습니다 ㅎㅎ

WITH TMP1 AS (
    SELECT '1' S_ID, 'kang' S_NM FROM DUAL UNION ALL
    SELECT '2' S_ID, 'kimkukjin' S_NM FROM DUAL UNION ALL
    SELECT '3' S_ID, 'kimkildong' S_NM FROM DUAL UNION ALL
    SELECT '4' S_ID, 'seojinsu' S_NM FROM DUAL)

, TMP2 AS (
    SELECT '1' C_ID, 'DataBase' C_NM FROM DUAL UNION ALL
    SELECT '2' C_ID, 'Java' C_NM FROM DUAL)

, TMP3 AS (
    SELECT '1' S_ID,  '1' C_ID ,  1 CHASU FROM DUAL UNION ALL
    SELECT '1' S_ID,  '1' C_ID ,  3 CHASU FROM DUAL UNION ALL
    SELECT '1' S_ID,  '2' C_ID ,  2 CHASU FROM DUAL UNION ALL
    SELECT '2' S_ID,  '1' C_ID ,  1 CHASU FROM DUAL UNION ALL
    SELECT '2' S_ID,  '1' C_ID ,  2 CHASU FROM DUAL UNION ALL
    SELECT '2' S_ID,  '1' C_ID ,  3 CHASU FROM DUAL UNION ALL
    SELECT '3' S_ID,  '2' C_ID ,  1 CHASU FROM DUAL UNION ALL
    SELECT '3' S_ID,  '2' C_ID ,  2 CHASU FROM DUAL UNION ALL
    SELECT '4' S_ID,  '1' C_ID ,  1 CHASU FROM DUAL)

SELECT DECODE(T.C_ID, 1, T.S_ID) 아이디,
       DECODE(T.C_ID, 1, DECODE(T.S_NM, 'kang', '강호동', 'kimkukjin', '유재석', 'kimkildong', '김제동', 'seojinsu', '김국진'))  성명,
       --T.C_ID,
       T.C_NM 스터디,
       MIN(DECODE(TMP3.CHASU, 1, '○', NULL)) AS "1차",
       MIN(DECODE(TMP3.CHASU, 2, '○', NULL)) AS "2차",
       MIN(DECODE(TMP3.CHASU, 3, '○', NULL)) AS "3차",
       COUNT(TMP3.CHASU) AS 참여횟수
FROM   (SELECT *
        FROM   TMP1, TMP2
       ) T, TMP3
WHERE  T.S_ID = TMP3.S_ID(+)
AND    T.C_ID = TMP3.C_ID(+)
GROUP BY T.S_ID, T.S_NM, T.C_ID, T.C_NM
ORDER BY T.S_ID, T.C_ID

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 퀴즈 및 튜닝 문제