데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

GP	No	원하는 순서order by
blue	k1	1
blue	k2	2
blue	k3	3
blue	k4	6
blue	k5	7
blue	k6	8
blue	k7	11
blue	k8	12
blue	k9	13
blue	k10	16
red	k11	4
red	k12	5
red	k13	9
red	k14	10
red	k15	14
red	k16	15
red	k17	17

WITH DT AS (

SELECT 'blue' AS GP, 'k1' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k2' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k3' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k4' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k5' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k6' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k7' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k8' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k9' AS NO FROM DUAL UNION ALL

SELECT 'blue' AS GP, 'k10' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k11' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k12' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k13' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k14' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k15' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k16' AS NO FROM DUAL UNION ALL

SELECT 'red' AS GP, 'k17' AS NO FROM DUAL

)

SELECT *

FROM DT

이 글에 대한 댓글이 총 3건 있습니다.

https://mjn5027.tistory.com/107

lucky님이 2023-06-16 23:48에 작성한 댓글입니다. Edit Ｘ

WITH dt AS

(

SELECT 'blue' gp, 'k1' no FROM dual

UNION ALL SELECT 'blue', 'k2'  FROM dual

UNION ALL SELECT 'blue', 'k3'  FROM dual

UNION ALL SELECT 'blue', 'k4'  FROM dual

UNION ALL SELECT 'blue', 'k5'  FROM dual

UNION ALL SELECT 'blue', 'k6'  FROM dual

UNION ALL SELECT 'blue', 'k7'  FROM dual

UNION ALL SELECT 'blue', 'k8'  FROM dual

UNION ALL SELECT 'blue', 'k9'  FROM dual

UNION ALL SELECT 'blue', 'k10' FROM dual

UNION ALL SELECT 'red' , 'k11' FROM dual

UNION ALL SELECT 'red' , 'k12' FROM dual

UNION ALL SELECT 'red' , 'k13' FROM dual

UNION ALL SELECT 'red' , 'k14' FROM dual

UNION ALL SELECT 'red' , 'k15' FROM dual

UNION ALL SELECT 'red' , 'k16' FROM dual

UNION ALL SELECT 'red' , 'k17' FROM dual

)

SELECT gp, no

     , ROW_NUMBER() OVER(ORDER BY CEIL(rn / x), gp, rn) rn

  FROM (SELECT gp, no

             , ROW_NUMBER() OVER(PARTITION BY gp ORDER BY LENGTH(no), no) rn

             , DECODE(gp, 'blue', 3, 2) x

          FROM dt

        )

;

마농(manon94)님이 2023-06-19 13:10에 작성한 댓글입니다.

감사합니다.

차상환님이 2023-06-25 21:46에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
41967	쿼리 질문드립니다! [1]	뽕구파파	2023-08-08	562
41966	SqL 도움 요청입니다. [2]	김진수	2023-07-25	880
41964	스칼라 서브쿼리를 아우터 조인으로 [1]	유경일	2023-07-12	1120
41963	조건에 따른 order by 설정 [3]	차상환	2023-06-16	3033
41962	GLOBAL TEMPORARY TABLE COMMIT 옵션 변경가능 한지요 ? [1]	이규동	2023-06-15	2938
41961	NLS_DATE_FORMAT변경관련 질문입니다.	우태열	2023-05-13	5067
41948	ORACLE DB SQL 쿼리 튜닝-8번째[DB 튜닝]	조장복	2023-03-21	6096

작업시간: 0.017초, 이곳 서비스는
PostgreSQL v16.4로 자료를 관리합니다