데이터베이스 질문과 답변
SELECT '1' AS SEQ, '1' AS cnt from dual
UNION ALL
SELECT '2' AS SEQ, '1' AS cnt from dual
UNION ALL
SELECT '3' AS SEQ, '1' AS cnt from dual
UNION ALL
SELECT '4' AS SEQ, '2' AS cnt from dual
UNION ALL
SELECT '5' AS SEQ, '2' AS cnt from dual
UNION ALL
SELECT '6' AS SEQ, '3' AS cnt from dual
UNION ALL
SELECT '7' AS SEQ, '4' AS cnt from dual
UNION ALL
SELECT '8' AS SEQ, '4' AS cnt from dual
:
:
위와 같은 데이타가 있을때
SEQ cnt
1 3
2 2
3 1
4 2
5 1
6 1
7 2
8 1
:
:
이런식으로 처리하고 싶습니다.
쿼리로 깔끔하게 정리하는 방법 있으면 알려주세요
잘 부탁드립니다.
by 야신
[2013.09.04 10:14:01]
count over 로 검색해 보세요
by 아린
[2013.09.04 10:23:22]
SELECT seq, cnt,
COUNT(*) OVER(PARTITION BY cnt) -
COUNT(*) OVER(PARTITION BY cnt) -
ROW_NUMBER() OVER(PARTITION BY cnt ORDER BY seq) + 1 cnt2
FROM t
by 순자
[2013.09.04 10:50:49]
SELECT seq, COUNT(CNT) OVER (PARTITION BY CNT) - (row_number() over(partition by cnt order by seq asc)- 1) cnt
FROM T
by 마농
[2013.09.04 11:27:22]
SELECT seq
, cnt
, ROW_NUMBER() OVER(PARTITION BY cnt ORDER BY seq DESC) cnt2
FROM t
ORDER BY seq
;
by 윤
[2013.09.30 17:37:54]
SELECT t.seq,t2.cnt1 FROM t,(SELECT cnt a,count(cnt)cnt1 FROM t GROUP BY cnt)t2 WHERE t2.A(+)=t.seq; --엌,,,이해를 잘못하고있었나보네요;; --seq의 해당하는숫자가 cnt쪽에 몇개인지 인줄 알았는데;;;; --혹시나해서 올려봅니다 ㅜㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.