데이터베이스 질문과 답변

테이블 간 JOIN 카운드 문의 0 5 980

by 오라클왕따 [Oracle 기초] [2014.06.09 15:54:26]

안녕하세요~ 오라클클럽을 통해 많은 도움을 받고 있는데요^^

B테이블에 있는 KEY값들의 개수를 A테이블 KEY값과 조인하여 A와 같은 KEY의 카운터를 내보려고 합니다. 

                     A테이블 　              B테이블
           key            key    soonwi
15471996603396   15471996603396 1
15471996617649   15471996603396 1-1
　 　 15471996603396 2
　 　 15471996603396 2-1
　 　 15471996603396 3
　 　 15471996603396 3-1
    15471996617649 1
    15471996617649 2
    15471996617649 3

아래와 같이 결과를 내보려고 하는데 문의를 드립니다~^^ 무더위 조심하시고 모기도 조심하시길 바래요~

                      결과
            key       count
15471996603396            6
15471996617649            3
　 　
　 　

 

 

by 아발란체 [2014.06.09 16:01:10] 
WITH T1 AS (
  SELECT 15471996603396 AS key FROM DUAL UNION ALL
  SELECT 15471996617649 FROM DUAL
), T2 AS (
  SELECT 15471996603396 AS key, '1' AS soonwi FROM DUAL UNION ALL
  SELECT 15471996603396, '1-1' FROM DUAL UNION ALL
  SELECT 15471996603396, '2' FROM DUAL UNION ALL
  SELECT 15471996603396, '2-1' FROM DUAL UNION ALL
  SELECT 15471996603396, '3' FROM DUAL UNION ALL
  SELECT 15471996603396, '3-1' FROM DUAL UNION ALL
  SELECT 15471996617649, '1' FROM DUAL UNION ALL
  SELECT 15471996617649, '2' FROM DUAL UNION ALL
  SELECT 15471996617649, '3' FROM DUAL 
)
SELECT
  key, COUNT(*)
FROM
  T2
WHERE
  EXISTS(SELECT 1 FROM T1 WHERE key = T2.key)
GROUP BY 
  key

 

by 오라클왕따 [2014.06.09 17:29:46]

감사합니다~^^ 아발란체님~ 유용하게 잘 활용하겠습니다~^^

by 아발란체 [2014.06.09 18:01:16]

도움이 되셨다니 다행입니다. ^^

by 윤 [2014.07.03 13:06:28]

WITH T1 AS (
  SELECT 15471996603396 AS key FROM DUAL UNION ALL
  SELECT 15471996617649 FROM DUAL
), T2 AS (
  SELECT 15471996603396 AS key, '1' AS soonwi FROM DUAL UNION ALL
  SELECT 15471996603396, '1-1' FROM DUAL UNION ALL
  SELECT 15471996603396, '2' FROM DUAL UNION ALL
  SELECT 15471996603396, '2-1' FROM DUAL UNION ALL
  SELECT 15471996603396, '3' FROM DUAL UNION ALL
  SELECT 15471996603396, '3-1' FROM DUAL UNION ALL
  SELECT 15471996617649, '1' FROM DUAL UNION ALL
  SELECT 15471996617649, '2' FROM DUAL UNION ALL
  SELECT 15471996617649, '3' FROM DUAL
)
select t1.key,count(t1.key)cnt from t1,t2
where t1.key=t2.key
group by t1.key
order by t1.key;

by 시골간아찌 [2014.07.03 14:07:04] 
SELECT  
    B.VAL1 , COUNT(B.VAL2)
FROM 
(SELECT '15471996603396' VAL1 FROM DUAL UNION ALL
SELECT '15471996617649'  FROM DUAL) A ,
(SELECT '15471996603396' VAL1 , 1 VAL2  FROM DUAL UNION ALL
SELECT '15471996603396' , 1-1   FROM DUAL UNION ALL
SELECT '15471996603396' , 2     FROM DUAL UNION ALL
SELECT '15471996603396' , 2-1   FROM DUAL UNION ALL
SELECT '15471996603396' , 3     FROM DUAL UNION ALL
SELECT '15471996603396' , 3-1   FROM DUAL UNION ALL
SELECT '15471996617649' , 1     FROM DUAL UNION ALL
SELECT '15471996617649' , 2     FROM DUAL UNION ALL
SELECT '15471996617649' , 3     FROM DUAL UNION ALL
SELECT '15471996617659' , 1     FROM DUAL ) B 
WHERE A.VAL1 = B.VAL1
GROUP BY B.VAL1

 

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