|
SELECT CNT , TEST1, TEST2
, CASE WHEN TEST1 <> 0 THEN TEST2-TEST1 END AS RESULT
FROM (
SELECT '1' AS CNT, 1 AS TEST1, 10 AS TEST2 FROM DUAL
UNION ALL
SELECT '1' AS CNT, 1 AS TEST1, 20 AS TEST2 FROM DUAL
UNION ALL
SELECT '1' AS CNT, 1 AS TEST1, 30 AS TEST2 FROM DUAL
UNION ALL
SELECT '2' AS CNT, 2 AS TEST1, 10 AS TEST2 FROM DUAL
UNION ALL
SELECT '2' AS CNT, 2 AS TEST1, 20 AS TEST2 FROM DUAL
UNION ALL
SELECT '2' AS CNT, 2 AS TEST1, 30 AS TEST2 FROM DUAL
)
order by cnt, test2 desc
;
제가 원하는 쿼리 결과값은 아래와 같습니다. "cnt" 별로 test2 값이 제일 큰 값에 test1을 빼는 값을 result로 표시하는 값을
원하는데 위에 쿼리에서 어떻게 수정해야 할까요?
|
cnt
|
test1 |
test2 |
result |
| 1 |
1 |
30 |
29 |
| 1 |
1 |
20 |
20 |
| 1 |
1 |
10 |
10 |
| 2 |
2 |
30 |
28 |
| 2 |
2 |
20 |
20 |
| 2 |
2 |
10 |
10 |
|
