ROW_NUMBER() 데이터의 순번 출력

순번 출력을 위해 ROW_NUMBER()를 사용한다. 이는 ANSI 표준으로 MS-SQL도 사용할 수 있다. 단, 이때 오라클에서 'PARTITION BY DT' 사용시 해당순번은 모두 1이 나오게 된다. 이유는 로우가 1개인 그룹으로 묶인 상태에서 또다시 DT의 묶음단위로 순번을 새로 매기기 때문이다.

WITH T1 AS(
    SELECT '1' RN, '20130711' DT, '23' CNT FROM DUAL UNION ALL
    SELECT '2' RN, '20130711' DT, '25' CNT FROM DUAL UNION ALL
    SELECT '3' RN, '20130711' DT, '30' CNT FROM DUAL UNION ALL
    SELECT '4' RN, '20130712' DT, '43' CNT FROM DUAL UNION ALL
    SELECT '5' RN, '20130712' DT, '15' CNT FROM DUAL UNION ALL
    SELECT '6' RN, '20130712' DT, '13' CNT FROM DUAL UNION ALL
    SELECT '7' RN, '20130712' DT, '28' CNT FROM DUAL UNION ALL
    SELECT '1' RN, '20130712' DT, '10' CNT FROM DUAL UNION ALL
    SELECT '2' RN, '20130713' DT, '10' CNT FROM DUAL UNION ALL
    SELECT '3' RN, '20130713' DT, '20' CNT FROM DUAL UNION ALL
    SELECT '4' RN, '20130715' DT, '30' CNT FROM DUAL UNION ALL
    SELECT '5' RN, '20130715' DT, '40' CNT FROM DUAL UNION ALL
    SELECT '6' RN, '20130720' DT, '50' CNT FROM DUAL UNION ALL
    SELECT '7' RN, '20130723' DT, '60' CNT FROM DUAL
)
SELECT DT
      ,SUM(CNT) AS CNT_OF_SUM
      ,ROW_NUMBER() OVER(ORDER BY DT) AS RN
  FROM T1
 GROUP BY DT

sql 데이터 옆으로 붙이기

옆으로 붙이기란 결국 조인이 되는 컬럼값을 일치시키는 것이다.


WITH T1 AS(
    SELECT '1' RN, '20130711' DT, '23' CNT FROM DUAL UNION ALL
    SELECT '2' RN, '20130711' DT, '25' CNT FROM DUAL UNION ALL
    SELECT '3' RN, '20130711' DT, '30' CNT FROM DUAL UNION ALL
    SELECT '4' RN, '20130712' DT, '43' CNT FROM DUAL UNION ALL
    SELECT '5' RN, '20130712' DT, '15' CNT FROM DUAL UNION ALL
    SELECT '6' RN, '20130712' DT, '13' CNT FROM DUAL UNION ALL
    SELECT '7' RN, '20130712' DT, '28' CNT FROM DUAL
)
,T2 AS(
    SELECT '1' RN, '20130718' DT, '10' CNT FROM DUAL UNION ALL
    SELECT '2' RN, '20130719' DT, '10' CNT FROM DUAL UNION ALL
    SELECT '3' RN, '20130720' DT, '20' CNT FROM DUAL UNION ALL
    SELECT '4' RN, '20130721' DT, '30' CNT FROM DUAL UNION ALL
    SELECT '5' RN, '20130722' DT, '40' CNT FROM DUAL UNION ALL
    SELECT '6' RN, '20130723' DT, '50' CNT FROM DUAL UNION ALL
    SELECT '7' RN, '20130724' DT, '60' CNT FROM DUAL
)
SELECT T1.DT
      ,T1.CNT
      ,T2.DT
      ,T2.CNT
  FROM T1
 INNER JOIN T2
    ON T1.RN = T2.RN
;