데이터베이스 질문과 답변
안녕하세요 학점별 점수테이블이있는데 이것을 점수구간으로 자를수있는 방법이있을까요?
WITH T1 AS
(
SELECT 'S+' AS GRADE, 100 AS SCORE FROM DUAL
UNION
SELECT 'S' , 95 FROM DUAL
UNION
SELECT 'A+' , 90 FROM DUAL
UNION
SELECT 'A' , 85 FROM DUAL
UNION
SELECT 'B+' , 80 FROM DUAL
UNION
SELECT 'B' , 75 FROM DUAL
UNION
SELECT 'C+' , 70 FROM DUAL
UNION
SELECT 'C' , 65 FROM DUAL
UNION
SELECT 'D+' , 60 FROM DUAL
ORDER BY 2 DESC
)
SELECT *
FROM T1
위같은 데이터가있을떄,
A라는 사람의 종합점수가 86.67이라고 가정하면, 85점이상 90점미만이므로 A라는 점수가 나오는데, T1테이블에 BETWEEN 조건을 걸수가 없어서, 각 점수별 구간을 조회하는 컬럼을 만들어야하는데, 잘모르겠습니다..ㅠ
T1테이블에서 이상,이하 구분을 잘라줄수있는 쿼리를 짤수있을까요?
by jkson
[2017.08.04 17:34:56]
WITH T1 AS
(
SELECT 'S+' AS GRADE, 100 AS SCORE FROM DUAL
UNION
SELECT 'S' , 95 FROM DUAL
UNION
SELECT 'A+' , 90 FROM DUAL
UNION
SELECT 'A' , 85 FROM DUAL
UNION
SELECT 'B+' , 80 FROM DUAL
UNION
SELECT 'B' , 75 FROM DUAL
UNION
SELECT 'C+' , 70 FROM DUAL
UNION
SELECT 'C' , 65 FROM DUAL
UNION
SELECT 'D+' , 60 FROM DUAL
ORDER BY 2 DESC
)
,SCORE AS
(
SELECT 'SON' ID, 86.67 SCORE FROM DUAL UNION ALL
SELECT 'KIM' ID, 65.11 SCORE FROM DUAL
)
SELECT ID, MIN(GRADE)
FROM SCORE A, T1 B
WHERE A.SCORE > B.SCORE
GROUP BY ID
by 우리집아찌
[2017.08.04 17:44:55]
GRADE가 S도 있어...
by 우리집아찌
[2017.08.04 17:38:08]
WITH T1 AS
(
SELECT 'S+' AS GRADE, 100 AS SCORE FROM DUAL
UNION
SELECT 'S' , 95 FROM DUAL
UNION
SELECT 'A+' , 90 FROM DUAL
UNION
SELECT 'A' , 85 FROM DUAL
UNION
SELECT 'B+' , 80 FROM DUAL
UNION
SELECT 'B' , 75 FROM DUAL
UNION
SELECT 'C+' , 70 FROM DUAL
UNION
SELECT 'C' , 65 FROM DUAL
UNION
SELECT 'D+' , 60 FROM DUAL
ORDER BY 2 DESC
), T2 AS (
SELECT 86.67 SCORE FROM DUAL UNION ALL
SELECT 75.3 SCORE FROM DUAL UNION ALL
SELECT 20.2 SCORE FROM DUAL UNION ALL
SELECT 100 SCORE FROM DUAL
)
SELECT *
FROM (SELECT GRADE , SCORE AS FR_SCORE , NVL(LAG(SCORE) OVER(ORDER BY SCORE ASC ),0) AS TO_SCORE
FROM T1
) A ,
T2
WHERE T2.SCORE <= FR_SCORE AND T2.SCORE > TO_SCORE
by 우리집아찌
[2017.08.04 17:42:13]
짜고 보니 이상하네요. 86.67이 A면 100점만 S+ 인가요?
60점 이하는??
by 엘시아
[2017.08.04 17:46:48]
네 연습용으로 테스트한거라서 ㅎㅎ.. 100점일때만 S+로 한거구 60점이하는 D+로.. 하게 하려구욥
감사합니다^^
by jkson
[2017.08.04 17:39:50]
그런데 점수표를 저렇게 관리하시는 것보다
FROM ~ TO로 관리하시는 게 성능도 더 좋고 관리하시기도 편할겁니다.
WITH T1 AS
(
SELECT 'S+' GRADE, 100 FSCORE, 100 TSCORE FROM DUAL
UNION
SELECT 'S' , 95, 100 FROM DUAL
UNION
SELECT 'A+' ,90, 95 FROM DUAL
UNION
SELECT 'A' ,85, 90 FROM DUAL
UNION
SELECT 'B+' ,80, 85 FROM DUAL
UNION
SELECT 'B' ,75, 80 FROM DUAL
UNION
SELECT 'C+' ,70, 75 FROM DUAL
UNION
SELECT 'C' ,65, 70 FROM DUAL
UNION
SELECT 'D+' ,0, 65 FROM DUAL
ORDER BY 2 DESC
)
,SCORE AS
(
SELECT 'SON' ID, 86.67 SCORE FROM DUAL UNION ALL
SELECT 'KIM' ID, 65.11 SCORE FROM DUAL
)
SELECT ID, B.GRADE
FROM SCORE A, T1 B
WHERE A.SCORE >= B.FSCORE
AND A.SCORE < B.TSCORE
댓글 달고 보니 아찌님도 같은 방법으로 만들어주셨네요.
by 엘시아
[2017.08.04 17:47:35]
jkson님 아찌님 모두 감사합니다^^
채택은 먼저해주신 jkson으로 했습니다..ㅎㅎㅎ..
) 버튼을 클릭하여 작성 하시면 됩니다.