위와 같은 정보가 있습니다. 가운데 있는 점수를 기준으로.. 몇등급 금액 얼마..이걸 가지고 오려합니다..
좋은..유용한 팁이 있을까요..
-- 1. Code 테이블을 만들어 관리하는 방법 WITH code_t AS ( SELECT '등급표시불가' grd, 0 sp, 69 ep, Null amt FROM dual UNION ALL SELECT 'A', 120, 999, 20000000 FROM dual UNION ALL SELECT 'B', 90, 119, 10000000 FROM dual UNION ALL SELECT 'C', 70, 89, 5000000 FROM dual ) , data_t AS ( SELECT 130 p FROM dual UNION ALL SELECT 100 FROM dual UNION ALL SELECT 80 FROM dual UNION ALL SELECT 60 FROM dual ) SELECT d.p, c.grd, c.amt FROM data_t d , code_t c WHERE d.p BETWEEN c.sp AND c.ep ;
-- 2. 그냥 Case 문으로 하는 방법 WITH data_t AS ( SELECT 130 p FROM dual UNION ALL SELECT 100 FROM dual UNION ALL SELECT 80 FROM dual UNION ALL SELECT 60 FROM dual ) SELECT p , CASE WHEN p >= 120 THEN 'A' WHEN p >= 90 AND p < 120 THEN 'B' WHEN p >= 70 AND p < 90 THEN 'C' WHEN p < 70 THEN '등급표시불가' END AS grd , CASE WHEN p >= 120 THEN 20000000 WHEN p >= 90 AND p < 120 THEN 10000000 WHEN p >= 70 AND p < 90 THEN 5000000 WHEN p < 70 THEN null END AS amt FROM data_t ;