꿈꾸는 개발자, DBA 커뮤니티 구루비

특정 조건일때 정렬조건 변경

by 구미구미 [Oracle 기초] oracle [2021.08.09 15:55:26]

현재 아래와 같이 쿼리를 작성하였는데요

WITH t AS
(
SELECT '1' seq,  '00001' NUM, '20210721' expire_date, '20210101' issue_date  FROM dual
UNION ALL SELECT '1' seq , '00002' NUM,  '20211015' expire_date, '20200101' issue_date FROM dual
UNION ALL SELECT '2' seq , '00003' NUM,  '20210404' expire_date, '20200202' issue_date FROM dual
UNION ALL SELECT '3' seq , '00004' NUM,  '99991231' expire_date, '20200303' issue_date FROM dual
UNION ALL SELECT '3' seq , '00005' NUM,  '99991231' expire_date, '20210303' issue_date FROM dual
UNION ALL SELECT '4' seq , '00006' NUM,  '20220101' expire_date, '20190404' issue_date FROM dual
UNION ALL SELECT '4' seq , '00007' NUM,  '20211031' expire_date, '20200404' issue_date FROM dual
UNION ALL SELECT '4' seq , '00008' NUM, '20301231' expire_date, '20210404' issue_date FROM dual
)
SELECT seq, NUM, expire_date, issue_date
  FROM t
  order by seq desc, expire_date asc, issue_date desc
;

seq	num	expire_date	issue_date
4	7	20211031	20200404
4	6	20220101	20190404
4	8	20301231	20210404
3	5	99991231	20210303
3	4	99991231	20200303
2	3	20210404	20200202
1	1	20210721	20210101
1	2	20211015	20200101

현재는 이렇게 되어있는데요!

seq가 3,4 일 경우에는 화면의 버튼에 따라 order by가 현재와 동일하지만

1) order by expire_date desc

seq	num	expire_date	issue_date
4	7	20211031	20200404
4	6	20220101	20190404
4	8	20301231	20210404
3	5	99991231	20210303
3	4	99991231	20200303
2	3	20210404	20200202
1	1	20210721	20210101
1	2	20211015	20200101

2) order by issue_date asc

seq	num	expire_date	issue_date
4	8	20301231	20210404
3	5	99991231	20210303
4	7	20211031	20200404
3	4	99991231	20200303
4	6	20220101	20190404
2	3	20210404	20200101
1	1	20210721	20210101
1	2	20211015	20200101

3)order by issue_date desc

seq	num	expire_date	issue_date
4	6	20220101	20190404
3	4	99991231	20200303
4	7	20211031	20200404
3	5	99991231	20210303
4	8	20301231	20210404
2	3	20210404	20200101
1	1	20210721	20210101
1	2	20211015	20200101

이런식으로 하고 싶은데요 ㅠㅠ

그대로 seq1,2는 현 조건 유지 하고 3,4 일 경우에만 오더바이 조건을 변경 할 수 있을까요?ㅠㅠ

by 동동동 [2021.08.09 16:32:37]

seq 는 1,2,3,4 밖에 없는 건가요?

by 마농 [2021.08.09 17:44:54]

WITH t AS
(
SELECT 1 seq, '00001' num, '20210721' expire_date, '20210101' issue_date FROM dual
UNION ALL SELECT 1, '00002', '20211015', '20200101' FROM dual
UNION ALL SELECT 2, '00003', '20210404', '20200202' FROM dual
UNION ALL SELECT 3, '00004', '99991231', '20200303' FROM dual
UNION ALL SELECT 3, '00005', '99991231', '20210303' FROM dual
UNION ALL SELECT 4, '00006', '20220101', '20190404' FROM dual
UNION ALL SELECT 4, '00007', '20211031', '20200404' FROM dual
UNION ALL SELECT 4, '00008', '20301231', '20210404' FROM dual
)
SELECT seq, num, expire_date, issue_date
  FROM t
 ORDER BY CASE WHEN seq IN (1, 2) THEN seq  ELSE 9 END DESC
        , CASE WHEN seq IN (1, 2) THEN expire_date END ASC
        , CASE WHEN seq IN (1, 2) THEN issue_date  END DESC
        , CASE WHEN seq IN (3, 4) THEN issue_date  END DESC
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변