Oracle SQL 우선순위 정하는 쿼리 질문드려요3
- 에페수스
- 등록일 2019-01-22 19:01
- 조회수 533
|
||||||||||||||||||
파워링크 
등록안내
등록안내
|
 0
 0
봄-봄
이런건 펑션으로 구현하는게 일반적인데..굳이 꼭 한방 select로 하고 싶다면...
WITH SAMPLE_DATA AS ( SELECT '1' AS SEQ, 'aa' AS AA, '' AS BB, 'cc' AS CC, '' AS DD FROM DUAL UNION ALL SELECT '2' AS SEQ, 'aa' AS AA, 'bb' AS BB, '' AS CC, '' AS DD FROM DUAL UNION ALL SELECT '3' AS SEQ, 'aa' AS AA, '' AS BB, '' AS CC, 'dd' AS DD FROM DUAL ) ,TMP AS ( SELECT NVL2(AA,1000,0) + NVL2(BB,100,0) + NVL2(CC,10,0) + NVL2(DD,1,0) AS RANK_VALUE ,SEQ FROM SAMPLE_DATA ) SELECT CASE WHEN BB IS NULL THEN AA WHEN CC IS NULL THEN BB WHEN DD IS NULL THEN CC ELSE DD END AS RESULT_VALUE FROM SAMPLE_DATA A WHERE SEQ = ( SELECT MAX(SEQ) KEEP(DENSE_RANK LAST ORDER BY RANK_VALUE) AS RESULT_RNUM FROM TMP ) |
|
0
0
ciwciw
Select NVL2(AA, 10000000, 0) + NVL2(BB, 1000000, 0) + NVL2(CC, 100000, 0) + NVL2(DD, 10000, 0) + NVL2(EE, 1000, 0) + NVL2(FF, 100, 0) + NVL2(GG, 10, 0) + NVL2(HH, 1, 0)
From DataTable Order by NVL2(AA, 10000000, 0) + NVL2(BB, 1000000, 0) + NVL2(CC, 100000, 0) + NVL2(DD, 10000, 0) + NVL2(EE, 1000, 0) + NVL2(FF, 100, 0) + NVL2(GG, 10, 0) + NVL2(HH, 1, 0) desc |
|
|
|
 |
|
| △ 이전글▽ 다음글 | 목록보기 |
|
첫번째는 1000, 두번쨰는 100 세번째 10....
이런식으로 하면 될듯...