데이터베이스 질문과 답변
해당값이 없을 경우의 특정 칼람취득
1
2
505
by 김선우 [2021.12.15 17:31:01]
vcd / scd / filename 로 구성되어 있는 테이블이 있습니다.
03D,0000,aaa.txt
03D,0001,aaa.txt
03D,0002,aaa.txt
03D, ,bbb.txt
03D,0003,bbb.txt
03D,0000,ccc.txt
03D,0001,ccc.txt
03D,0002,ddd.txt
03D,0005,ddd.txt
filename단위로 첫번째 레코드의 scd의 값이 0000가 아닌 파일명을 취득하려고 합니다.
위의 경우 결과값은
bbb.txt
ddd.txt
잘 부탁드립니다.
by pajama
[2021.12.15 18:16:41]
min값 비교했습니다~
select filename from t group by vcd, filename having min(scd) <> '0000';
by 동동동
[2021.12.16 08:42:25]
WITH TMP (VCD, SCD, FILENAME) AS (
SELECT '03D', '0000', 'AAA.TXT' FROM DUAL UNION ALL
SELECT '03D', '0001', 'AAA.TXT' FROM DUAL UNION ALL
SELECT '03D', '0002', 'AAA.TXT' FROM DUAL UNION ALL
SELECT '03D', ' ', 'BBB.TXT' FROM DUAL UNION ALL
SELECT '03D', '0003', 'BBB.TXT' FROM DUAL UNION ALL
SELECT '03D', '0000', 'CCC.TXT' FROM DUAL UNION ALL
SELECT '03D', '0001', 'CCC.TXT' FROM DUAL UNION ALL
SELECT '03D', '0002', 'DDD.TXT' FROM DUAL UNION ALL
SELECT '03D', '0005', 'DDD.TXT' FROM DUAL
)
SELECT DISTINCT FILENAME
FROM TMP A
WHERE NOT EXISTS (
SELECT 'X'
FROM TMP B
WHERE B.SCD = '0000'
AND B.FILENAME = A.FILENAME)
) 버튼을 클릭하여 작성 하시면 됩니다.