쿼리문의드립니다. 0 3 164

by DB초보자입니다 [Oracle 기초] [2018.02.09 15:33:04]


with  temp as
(
SELECT 20 AS AGE	, 92   AS VALUE FROM DUAL UNION ALL
SELECT 21 AS AGE	,103   AS VALUE FROM DUAL UNION ALL
SELECT 22 AS AGE	,115   AS VALUE FROM DUAL UNION ALL
SELECT 23 AS AGE	,126   AS VALUE FROM DUAL UNION ALL
SELECT 24 AS AGE	,137   AS VALUE FROM DUAL UNION ALL
SELECT 25 AS AGE	,149   AS VALUE FROM DUAL UNION ALL
SELECT 26 AS AGE	,160   AS VALUE FROM DUAL UNION ALL
SELECT 27 AS AGE	,172   AS VALUE FROM DUAL UNION ALL
SELECT 28 AS AGE	,211   AS VALUE FROM DUAL UNION ALL
SELECT 29 AS AGE	,250   AS VALUE FROM DUAL UNION ALL
SELECT 30 AS AGE	,289   AS VALUE FROM DUAL UNION ALL
SELECT 31 AS AGE	,328   AS VALUE FROM DUAL UNION ALL
SELECT 32 AS AGE	,368   AS VALUE FROM DUAL UNION ALL
SELECT 33 AS AGE	,402   AS VALUE FROM DUAL UNION ALL
SELECT 34 AS AGE	,437   AS VALUE FROM DUAL UNION ALL
SELECT 35 AS AGE	,472   AS VALUE FROM DUAL UNION ALL
SELECT 36 AS AGE	,507   AS VALUE FROM DUAL UNION ALL
SELECT 37 AS AGE	,542   AS VALUE FROM DUAL UNION ALL
SELECT 38 AS AGE	,646   AS VALUE FROM DUAL UNION ALL
SELECT 39 AS AGE	,750   AS VALUE FROM DUAL UNION ALL
SELECT 40 AS AGE	,854   AS VALUE FROM DUAL UNION ALL
SELECT 41 AS AGE	,958   AS VALUE FROM DUAL UNION ALL
SELECT 42 AS AGE	,1062  AS VALUE FROM DUAL UNION ALL
SELECT 43 AS AGE	,1143  AS VALUE FROM DUAL UNION ALL
SELECT 44 AS AGE	,1225  AS VALUE FROM DUAL UNION ALL
SELECT 45 AS AGE	,1307  AS VALUE FROM DUAL UNION ALL
SELECT 46 AS AGE	,1389  AS VALUE FROM DUAL UNION ALL
SELECT 47 AS AGE	,1471  AS VALUE FROM DUAL UNION ALL
SELECT 48 AS AGE	,1698  AS VALUE FROM DUAL UNION ALL
SELECT 49 AS AGE	,1926  AS VALUE FROM DUAL UNION ALL
SELECT 50 AS AGE	,2154  AS VALUE FROM DUAL UNION ALL
SELECT 51 AS AGE	,2382  AS VALUE FROM DUAL UNION ALL
SELECT 52 AS AGE	,2610  AS VALUE FROM DUAL UNION ALL
SELECT 53 AS AGE	,2905  AS VALUE FROM DUAL UNION ALL
SELECT 54 AS AGE	,3201  AS VALUE FROM DUAL UNION ALL
SELECT 55 AS AGE	,3496  AS VALUE FROM DUAL UNION ALL
SELECT 56 AS AGE	,3792  AS VALUE FROM DUAL UNION ALL
SELECT 57 AS AGE	,4088  AS VALUE FROM DUAL UNION ALL
SELECT 58 AS AGE	,4720  AS VALUE FROM DUAL UNION ALL
SELECT 59 AS AGE	,5353  AS VALUE FROM DUAL UNION ALL
SELECT 60 AS AGE	,5986  AS VALUE FROM DUAL UNION ALL
SELECT 61 AS AGE	,6619  AS VALUE FROM DUAL UNION ALL
SELECT 62 AS AGE	,7252  AS VALUE FROM DUAL UNION ALL
SELECT 63 AS AGE	,8001  AS VALUE FROM DUAL UNION ALL
SELECT 64 AS AGE	,8750  AS VALUE FROM DUAL UNION ALL
SELECT 65 AS AGE	,9499  AS VALUE FROM DUAL UNION ALL
SELECT 66 AS AGE	,10248 AS VALUE FROM DUAL UNION ALL
SELECT 67 AS AGE	,10997 AS VALUE FROM DUAL UNION ALL
SELECT 68 AS AGE	,11975 AS VALUE FROM DUAL UNION ALL
SELECT 69 AS AGE	,12953 AS VALUE FROM DUAL UNION ALL
SELECT 70 AS AGE	,13931 AS VALUE FROM DUAL UNION ALL
SELECT 71 AS AGE	,14909 AS VALUE FROM DUAL UNION ALL
SELECT 72 AS AGE	,15887 AS VALUE FROM DUAL UNION ALL
SELECT 73 AS AGE	,16307 AS VALUE FROM DUAL UNION ALL
SELECT 74 AS AGE	,16728 AS VALUE FROM DUAL UNION ALL
SELECT 75 AS AGE	,17148 AS VALUE FROM DUAL UNION ALL
SELECT 76 AS AGE	,17569 AS VALUE FROM DUAL UNION ALL
SELECT 77 AS AGE	,17990 AS VALUE FROM DUAL UNION ALL
SELECT 78 AS AGE	,18410 AS VALUE FROM DUAL UNION ALL
SELECT 79 AS AGE	,18831 AS VALUE FROM DUAL UNION ALL
SELECT 80 AS AGE	,19251 AS VALUE FROM DUAL UNION ALL
SELECT 81 AS AGE	,19672 AS VALUE FROM DUAL UNION ALL
SELECT 82 AS AGE	,20093 AS VALUE FROM DUAL UNION ALL
SELECT 83 AS AGE	,20513 AS VALUE FROM DUAL UNION ALL
SELECT 84 AS AGE	,20934 AS VALUE FROM DUAL UNION ALL
SELECT 85 AS AGE	,21354 AS VALUE FROM DUAL
)
select * from temp

UI단에서 3033이라는 값을 DB로 던져줍니다(값은 매번 변경됩니다.).

그럼 위의 temp 테이블에서 VALUE 와 비교해서 가장 적게 차이나는 AGE를 구하고싶습니다.

프로시저에서 LOOP를 돌면서 모든 값을 다 찾아야될까요??

ex)3044 - AGE(53)의VALUE2905 =  139로 가장 적은 차이가 납니다.

 

 

by 마농 [2018.02.09 16:02:31]

109 가 입력되었을 때? 가장 적게 차이나는 AGE 가 2개 동점인데? (21세, 22세)
동점 처리 기준이 따로 있나요?
결과가 어떻게 나와야 하나요?


by DB초보자입니다 [2018.02.09 16:34:42]

 

동점자가 발생 시 나이가 더 어린걸로 나오면됩니다!


by 마농 [2018.02.09 16:37:28]
SELECT *
  FROM (SELECT age, value
             , ABS(value - 109) x
          FROM temp
         ORDER BY x, age
        )
 WHERE ROWNUM = 1
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입