데이터베이스 질문과 답변
정렬순서별 그룹 정렬좀 도와주세요
1
2
2,155
by 봉 [2012.02.27 17:56:23]
WITH TEMP AS (
SELECT 'F' AS GRP, 1 AS NUM FROM DUAL UNION ALL
SELECT 'X' AS GRP, 2 AS NUM FROM DUAL UNION ALL
SELECT 'A' AS GRP, 3 AS NUM FROM DUAL UNION ALL
SELECT 'A' AS GRP, 4 AS NUM FROM DUAL UNION ALL
SELECT 'C' AS GRP, 5 AS NUM FROM DUAL UNION ALL
SELECT 'C' AS GRP, 6 AS NUM FROM DUAL UNION ALL
SELECT 'D' AS GRP, 7 AS NUM FROM DUAL UNION ALL
SELECT 'E' AS GRP, 8 AS NUM FROM DUAL
)
SELECT GRP
, NUM
FROM TEMP
GRP NUM 순서
F 1 1
X 2 2
A 3 3
A 4 3
C 5 4
C 6 4
D 7 5
E 8 6
제가 원하는 순서는 위에서 보시는 대로
NUM이라는 정렬 순서대로 나오되 DENSE_RANK처럼 동일 그룹은
같은 순번으로 나오는건데요
될거 같기도 하면서 잘 안되네요.. 도와주세요
by 마농
[2012.02.27 18:23:05]
SELECT grp, num
, DENSE_RANK() OVER(ORDER BY n) seq
FROM
(
SELECT grp, num
, MIN(num) OVER(PARTITION BY grp) n
FROM temp
)
;
SELECT grp, num
, COUNT(v) OVER(ORDER BY num) seq
FROM
(
SELECT grp, num
, NULLIF(grp, LAG(grp) OVER(ORDER BY num)) v
FROM temp
)
;
SELECT *
FROM temp
MODEL
DIMENSION BY (num)
MEASURES (grp, 1 seq)
IGNORE NAV
RULES (seq[ANY] = seq[CV()-1] + DECODE(grp[CV()], grp[CV()-1], 0, 1))
;
, DENSE_RANK() OVER(ORDER BY n) seq
FROM
(
SELECT grp, num
, MIN(num) OVER(PARTITION BY grp) n
FROM temp
)
;
SELECT grp, num
, COUNT(v) OVER(ORDER BY num) seq
FROM
(
SELECT grp, num
, NULLIF(grp, LAG(grp) OVER(ORDER BY num)) v
FROM temp
)
;
SELECT *
FROM temp
MODEL
DIMENSION BY (num)
MEASURES (grp, 1 seq)
IGNORE NAV
RULES (seq[ANY] = seq[CV()-1] + DECODE(grp[CV()], grp[CV()-1], 0, 1))
;
by 봉
[2012.02.27 18:42:06]
아.. 감사합니다
계속 한쿼리로 하려고 보니 안됬었나보네요
마지막 쿼리는 제 능력으론 쓰질 못하겠네요 -_-;;
계속 한쿼리로 하려고 보니 안됬었나보네요
마지막 쿼리는 제 능력으론 쓰질 못하겠네요 -_-;;
) 버튼을 클릭하여 작성 하시면 됩니다.