데이터베이스 질문과 답변
SELECT 1 AS NO1, 1 AS NO2 FROM DUAL UNION ALL SELECT 1 AS NO1, 1 AS NO2 FROM DUAL UNION ALL SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL SELECT 2 AS NO1, 3 AS NO2 FROM DUAL UNION ALL SELECT 2 AS NO1, 3 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 5 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 5 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 6 AS NO2 FROM DUAL UNION ALL SELECT 3 AS NO1, 6 AS NO2 FROM DUAL
안녕하세요
그룹별 RANK를 하고 싶은데 잘안되서 질문드립니다.
데이터가 NO1, NO2같이 있을때 NO3로 번호를 매기고 싶습니다.
NO1의 번호그룹별로 NO2값을 다시 그룹을 매겨서 번호를 1로 다시 주고 싶습니다.
NO1 NO2 NO3(원하는값)
1 1 1
1 1 1
2 2 1
2 2 1
2 2 1
2 3 2
2 3 2
3 4 1
3 4 1
3 4 1
3 5 2
3 5 2
3 6 3
3 6 3
감사합니다.
by 랑에1
[2017.10.12 17:49:18]
DENSE_RANK() OVER(PARTITION BY NO1 ORDER BY NO2) AS NO3
by 박군two
[2017.10.12 17:54:27]
WITH T AS (
SELECT 1 AS NO1, 1 AS NO2 FROM DUAL UNION ALL
SELECT 1 AS NO1, 1 AS NO2 FROM DUAL UNION ALL
SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL
SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL
SELECT 2 AS NO1, 2 AS NO2 FROM DUAL UNION ALL
SELECT 2 AS NO1, 3 AS NO2 FROM DUAL UNION ALL
SELECT 2 AS NO1, 3 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 4 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 5 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 5 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 6 AS NO2 FROM DUAL UNION ALL
SELECT 3 AS NO1, 6 AS NO2 FROM DUAL
)
SELECT NO1, NO2,
DENSE_RANK() OVER (PARTITION BY NO1 ORDER BY NO1, NO2) RANK
FROM T
by 엘시아
[2017.10.12 18:40:58]
이렇게 간단한 거였다니.. 더 해볼껄그랬네요 ㅎㅎ.. 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.