데이터베이스 질문과 답변
오라클 쿼리 질문드립니다...
0
5
2,231
by minrule [2023.08.02 11:36:16]
안녕하세요... 업무중에 쿼리짜다가 방법을 못찾겠어서 질문 드립니다.
SELECT LOTID
, SEQ
, REP_LOC
, LOC
, CODE
, QTY
FROM TB1
WHERE LOTID = 'A'
의 조회결과로 아래와 같이 나올때
| LOTID | SEQ | REP_LOC | LOC | CODE | QTY |
| A | 1 | L1 | |||
| A | 2 | L2 | CODE1 | 200 | |
| A | 3 | L3 | L3 | CODE2 | 1400 |
| A | 4 | L4 | L4 | ||
| A | 5 | L5 | |||
| A | 6 | L6 | L6 | CODE3 | 220 |
| A | 7 | L7 |
이렇게 REP_LOC가 비어있으면 이후 ROW중 REP_LOC값이 있는 ROW의 REP_LOC값이 표기되도록 하고싶은데요.
(이후 ROW중 REP_LOC값이 있는 ROW가 없으면 빈값 유지)
| LOTID | SEQ | REP_LOC | LOC | CODE | QTY |
| A | 1 | L3 | L1 | ||
| A | 2 | L3 | L2 | CODE1 | 200 |
| A | 3 | L3 | L3 | CODE2 | 1400 |
| A | 4 | L4 | L4 | ||
| A | 5 | L6 | L5 | ||
| A | 6 | L6 | L6 | CODE3 | 220 |
| A | 7 | L7 |
혹시 뭔가 방법이 있을까요??
by pajama
[2023.08.02 13:07:57]
안녕하세요. MIN OVER 를 사용해봤습니다.
WITH T (LOTID, SEQ, REP_LOC) AS ( SELECT 'A', 1, NULL FROM DUAL UNION ALL SELECT 'A', 2, NULL FROM DUAL UNION ALL SELECT 'A', 3, 'L3' FROM DUAL UNION ALL SELECT 'A', 4, 'L4' FROM DUAL UNION ALL SELECT 'A', 5, NULL FROM DUAL UNION ALL SELECT 'A', 6, 'L6' FROM DUAL UNION ALL SELECT 'A', 7, NULL FROM DUAL ) SELECT LOTID, SEQ, REP_LOC, MIN(REP_LOC) OVER (PARTITION BY LOTID ORDER BY SEQ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM T
by 동희
[2023.08.02 13:25:05]
pajama님 MIN OVER로 했을시 L3와 L4의 순서가 바뀌었을시 잘못 결과가 나오네요..
LEAD IGNORE NULLS 를 사용해 봤습니다.
WITH T (LOTID, SEQ, REP_LOC) AS (
SELECT 'A', 1, NULL FROM DUAL UNION ALL
SELECT 'A', 2, NULL FROM DUAL UNION ALL
SELECT 'A', 3, 'L4' FROM DUAL UNION ALL
SELECT 'A', 4, 'L3' FROM DUAL UNION ALL
SELECT 'A', 5, NULL FROM DUAL UNION ALL
SELECT 'A', 6, 'L6' FROM DUAL UNION ALL
SELECT 'A', 7, NULL FROM DUAL
)
SELECT LOTID, SEQ, REP_LOC
, MIN(REP_LOC) OVER (PARTITION BY LOTID ORDER BY SEQ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) REP_LOC2
, NVL(REP_LOC, LEAD(REP_LOC) IGNORE NULLS OVER(PARTITION BY LOTID ORDER BY SEQ)) REP_LOC3
FROM T
;
by 마농
[2023.08.02 13:33:46]
WITH tb1 AS
(
SELECT 'A' lotid, 1 seq, null rep_loc, 'L1' loc, null code, null qty FROM dual
UNION ALL SELECT 'A', 2, null, 'L2', 'CODE1', 200 FROM dual
UNION ALL SELECT 'A', 3, 'L3', 'L3', 'CODE2', 1400 FROM dual
UNION ALL SELECT 'A', 4, 'L4', 'L4', null , null FROM dual
UNION ALL SELECT 'A', 5, null, 'L5', null , null FROM dual
UNION ALL SELECT 'A', 6, 'L6', 'L6', 'CODE3', 220 FROM dual
UNION ALL SELECT 'A', 7, null, 'L7', null , null FROM dual
)
SELECT lotid
, seq
, LAST_VALUE(rep_loc) IGNORE NULLS OVER(PARTITION BY lotid ORDER BY seq DESC) rep_loc
, loc
, code
, qty
FROM tb1
WHERE lotid = 'A'
ORDER BY lotid, seq
;
by pajama
[2023.08.02 14:51:20]
동희님 마농님 좋은 방법 공유 감사합니다.
by minrule
[2023.08.02 16:34:44]
답변 달아주신 분들 모두 감사합니다~
) 버튼을 클릭하여 작성 하시면 됩니다.