데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 중복 저장된 코드의 갯수를 구하라
아래 테이블로부터 중복된 코드의 갯수를 출력하는 쿼리를 작성하세요.
중복된 코드는 'A'와 'B' 두개가 있으니 결과는 2 입니다.
WITH t AS
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT * FROM t;
[정답보기] <=== 트리플 클릭
SELECT COUNT(COUNT(*)) cnt
FROM t
GROUP BY cd
HAVING COUNT(*) > 1
;
by 서성우
[2009.11.09 15:33:50]
WITH t AS
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT Count(Decode(cnt,1,NULL,1))
FROM
(SELECT cd,Count(*) cnt FROM t
GROUP BY cd)
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT Count(Decode(cnt,1,NULL,1))
FROM
(SELECT cd,Count(*) cnt FROM t
GROUP BY cd)
by 서성우
[2009.11.09 15:34:55]
아
해답을 보니
두번써서 할수도 있군요...
해답을 보니
두번써서 할수도 있군요...
by 호야
[2009.11.11 15:57:01]
SELECT SUM(COUNT(*)) cnt FROM t
GROUP BY cd
HAVING COUNT(*) <=1
-_- 이거 아닌가요.
GROUP BY cd
HAVING COUNT(*) <=1
-_- 이거 아닌가요.
by 호야
[2009.11.11 15:59:21]
-_- 에이 또 틀렸네... 머리가 돌인가
by 이재현
[2009.12.01 11:07:34]
SELECT COUNT(MAX(RN)) AS RN
FROM (
SELECT CD, ROW_NUMBER() OVER(PARTITION BY CD ORDER BY CD) AS RN
FROM t
)
WHERE RN > 1
GROUP BY CD
FROM (
SELECT CD, ROW_NUMBER() OVER(PARTITION BY CD ORDER BY CD) AS RN
FROM t
)
WHERE RN > 1
GROUP BY CD
by 이재현
[2009.12.01 11:08:55]
저도 돌인듯..ㅠ
by fallacy
[2009.12.04 21:01:07]
WITH t AS
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT CD, COUNT(*) FROM t GROUP BY CD HAVING COUNT(*) > 0 ORDER BY CD;
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT CD, COUNT(*) FROM t GROUP BY CD HAVING COUNT(*) > 0 ORDER BY CD;
by 손님
[2009.12.23 10:21:34]
selct count(*) from
(select cd ,count(*) cnt from t group by cd having count(*) > 1)
제께 복잡한가여? ㅋㅋ
(select cd ,count(*) cnt from t group by cd having count(*) > 1)
제께 복잡한가여? ㅋㅋ
by finecomp
[2009.12.23 15:24:26]
답에 비해 그리 복잡해 보이진 않습니다...;
selct, *, from, cd , (, ) 등이 더 쓰였을 뿐...ㅋㅋ
selct, *, from, cd , (, ) 등이 더 쓰였을 뿐...ㅋㅋ
by 세븐블랙홀
[2009.12.29 15:59:06]
WITH t AS
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
select count(num)
from (
SELECT cd
,count(cd) as num
FROM t
group by cd
)
where num >= 2
답을 보니 허미.... 저리 간단한 방법이 다 있네요 ~~^^
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
select count(num)
from (
SELECT cd
,count(cd) as num
FROM t
group by cd
)
where num >= 2
답을 보니 허미.... 저리 간단한 방법이 다 있네요 ~~^^
by 세븐블랙홀
[2009.12.29 15:59:58]
WITH t AS
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
select count(num)
from (
SELECT cd
,count(cd) as num
FROM t
group by cd
)
where num >= 2
답을 보니 허미.. 저리 간단한 방법이 있다니...
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
select count(num)
from (
SELECT cd
,count(cd) as num
FROM t
group by cd
)
where num >= 2
답을 보니 허미.. 저리 간단한 방법이 있다니...
by 빈이
[2009.12.30 15:05:26]
count(count(*)) 첨본다...
by 동일
[2010.01.11 13:45:03]
WITH T AS
(
SELECT 'A' CD FROM DUAL
UNION ALL SELECT 'A' FROM DUAL
UNION ALL SELECT 'A' FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'C' FROM DUAL
UNION ALL SELECT 'D' FROM DUAL
)
SELECT COUNT(COUNT(*)) CNT
FROM T
GROUP BY CD
HAVING COUNT(*) > 1
(
SELECT 'A' CD FROM DUAL
UNION ALL SELECT 'A' FROM DUAL
UNION ALL SELECT 'A' FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'C' FROM DUAL
UNION ALL SELECT 'D' FROM DUAL
)
SELECT COUNT(COUNT(*)) CNT
FROM T
GROUP BY CD
HAVING COUNT(*) > 1
by 야쿠르트세컨드
[2012.12.27 00:40:39]
SELECT COUNT(cnt) FROM(
SELECT COUNT(*) cnt
FROM t
GROUP BY cd
)
WHERE cnt > 1
by 밥이다
[2019.09.24 18:57:00]
WITH t AS
(
SELECT 'A' cd FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'A' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT COUNT(*) FROM (
SELECT CD,COUNT(*) FROM t
GROUP BY CD
HAVING COUNT(*) > 1)
) 버튼을 클릭하여 작성 하시면 됩니다.