1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | --보다 간단히 짤 수 있을 것 같은데 멀리 돌아 짠 것 같네요. SELECT key , SUM (DECODE(flag, 1, var)) AS var1, SUM (DECODE(flag, 0, var)) AS var2 FROM ( SELECT key , var, MOD(ROW_NUMBER() OVER( ORDER BY KEY , VAR), 2) AS flag FROM ( SELECT key , DECODE(lv, 1, var1, 2, var2, 3, var3, var4) AS var, RANK() OVER(PARTITION BY key ORDER BY DECODE(lv, 1, var1, 2, var2, 3, var3, var4)) AS rank_no FROM TABLE1, ( SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 4) ) WHERE rank_no <= 2 ) GROUP BY key |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --다른 방법으로.. SELECT key , f1, MIN (val) FROM ( SELECT key , LEAST(VAR1, VAR2, VAR3, VAR4) AS F1, REGEXP_SUBSTR( REGEXP_REPLACE(VAR1|| '-' ||VAR2|| '-' ||VAR3|| '-' ||VAR4|| '-' , LEAST(VAR1, VAR2, VAR3, VAR4)|| '-' , '' , 1, 1), '[0-9]{1,}' , 1, LV ) AS VAL FROM TABLE1 T, ( SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 3) ) GROUP BY key , f1 ORDER BY key |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | WITH table1 AS ( SELECT 'A' pk, 2 v1, 2 v2, 5 v3, 4 v4 UNION ALL SELECT 'B' , 3, 2, 1, 5 UNION ALL SELECT 'C' , 1, 4, 2, 4 UNION ALL SELECT 'D' , 5, 5, 3, 3 UNION ALL SELECT 'E' , 3, 2, 1, 4 UNION ALL SELECT 'F' , 1, 2, 3, 4 ) , copy_t AS ( SELECT 1 lv UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) SELECT pk , MIN ( CASE WHEN rn = 1 THEN v END ) r1 , MIN ( CASE WHEN rn = 2 THEN v END ) r2 FROM ( SELECT pk, lv, v , ROW_NUMBER() OVER(PARTITION BY pk ORDER BY v) rn FROM ( SELECT pk, lv , CASE lv WHEN 1 THEN v1 WHEN 2 THEN v2 WHEN 3 THEN v3 WHEN 4 THEN v4 END v FROM table1 , copy_t ) a ) b GROUP BY pk ; |