데이터베이스 질문과 답변
테이블 A
칼럼1 칼럼2 칼럼3 칼럼4 칼럼5
A B C 103 100 (삭제)
A B C 104 200 (변경후200+100=300)
A B F 103 300 (삭제)
A B F 104 400 (변경후 300+400=700)
이와같이 칼럼 1,2,3이 같은 데이타 중에서,
칼럼4의 103의 값 100(칼럼5)를 칼럼4의 104의 칼럼5에 더하는 sql작성 가능할까요?
by 분당아재
[2025.09.09 17:29:21]
WITH TB_TEMP AS (
SELECT 'A' AS COL1, 'B' AS COL2, 'C' AS COL3, '103' AS COL4, 100 AS COL5 UNION ALL
SELECT 'A' AS COL1, 'B' AS COL2, 'C' AS COL3, '104' AS COL4, 200 AS COL5 UNION ALL
SELECT 'A' AS COL1, 'B' AS COL2, 'F' AS COL3, '103' AS COL4, 300 AS COL5 UNION ALL
SELECT 'A' AS COL1, 'B' AS COL2, 'F' AS COL3, '104' AS COL4, 400 AS COL5
)
SELECT COL1, COL2, COL3, COL4
,CASE WHEN RNUM = 1 THEN COL5 ELSE NULL END AS COL5
FROM
(
SELECT COL1, COL2, COL3, COL4
,SUM(COL5) OVER(PARTITION BY COL1, COL2, COL3 ORDER BY COL4) AS COL5
,ROW_NUMBER() OVER(PARTITION BY COL1, COL2, COL3 ORDER BY COL4 DESC) AS RNUM
FROM TB_TEMP
) T
ORDER BY COL1, COL2, COL3, RNUM DESC
by yeszeus
[2025.09.09 20:11:15]
전문가님..감사합니다
by yeszeus
[2025.09.09 21:10:52]
'A' 'B' 'C' 'F' 가 어떤값인지 모르는데
여기에 실제칼럼명을 넣어도되는지요?
by jobbu
[2025.09.10 09:04:44]
네, 실제 컬럼값을 넣어도 적용됩니다.
ROW_NUMBER(), 집계 윈도우 함수 부분이 어떤 역할을 하는지는
검색해보시는걸 권해드립니다.
by 마농
[2025.09.10 09:50:34]
CREATE TABLE test1
AS
SELECT 'A' c1, 'B' c2, 'C' c3, 103 c4, 100 v1 FROM dual
UNION ALL SELECT 'A', 'B', 'C', 104, 200 FROM dual
UNION ALL SELECT 'A', 'B', 'F', 103, 300 FROM dual
UNION ALL SELECT 'A', 'B', 'F', 104, 400 FROM dual
;
SELECT * FROM test1;
A B C 103 100
A B C 104 200
A B F 103 300
A B F 104 400
MERGE INTO test1 a
USING (SELECT c1, c2, c3, c4, v1
, ROW_NUMBER() OVER(PARTITION BY c1, c2, c3 ORDER BY c4) rn
, COUNT(*) OVER(PARTITION BY c1, c2, c3) cnt
, SUM(v1) OVER(PARTITION BY c1, c2, c3) v2
FROM test1
) b
ON (a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3 AND a.c4 = b.c4)
WHEN MATCHED THEN
UPDATE
SET v1 = b.v2
DELETE
WHERE b.rn != b.cnt
;
SELECT * FROM test1;
A B C 104 300
A B F 104 700
COMMIT;