데이터베이스 질문과 답변
by lgxj20 [2019.10.10 18:29:29]
select '20190101' as "전표일자"
,'00001' as "전표순번"
,'00001' as "전표상세번호"
,0 as "차변"
,0 as "대변"
from dual
union all
select '20190101' as "전표일자"
,'00001' as "전표순번"
,'00002' as "전표상세번호"
,0 as "차변"
,0 as "대변"
from dual
union all
select '20190101' as "전표일자"
,'00001' as "전표순번"
,'00003' as "전표상세번호"
,0 as "차변"
,0 as "대변"
from dual
/************/
union all
select '20190101' as "전표일자"
,'00002' as "전표순번"
,'00001' as "전표상세번호"
,-200 as "차변"
,0 as "대변"
from dual
union all
select '20190101' as "전표일자"
,'00002' as "전표순번"
,'00002' as "전표상세번호"
,200 as "차변"
,0 as "대변"
from dual
이렇게 데이타가 있을데 전표순번 00001 이나 00002 둘다 각 차변 대변 합이 0인데요
1번전표는 모든 금액이 0이고 --> 문제 전표
2번 전표는 금액이 0이 아닌 전표 --> 문제없는 전표
이런 데이타를 조회할 방법이 있을까요?
저는 우선 전표일,전표순번 합이 0인 전표중에 차변 대변을 sign을 걸어서 찾을라고 생각했는데 생각보다 잘안되네요
group by 전표일자, 전표순번
having max(차변)=0 and max(대변)=0
| 차변 | 대변 |
| 0 | 0 |
| -200 | -200 |
이런 값도 있을 수 있겠네요.
group by 전표일자, 전표순번
having max(abs(차변))=0 and max(abs(대변))=0
WITH t AS
(
SELECT '20190101' 전표일자, '00001' 전표순번, '00001' 전표상세번호, 0 차변, 0 대변 FROM dual
UNION ALL SELECT '20190101', '00001', '00002', 0, 0 FROM dual
UNION ALL SELECT '20190101', '00001', '00003', 0, 0 FROM dual
UNION ALL SELECT '20190101', '00002', '00001', -200, 0 FROM dual
UNION ALL SELECT '20190101', '00002', '00002', 200, 0 FROM dual
)
SELECT 전표일자
, 전표순번
FROM t
GROUP BY 전표일자, 전표순번
HAVING COUNT(CASE WHEN 차변 != 0 OR 대변 != 0 THEN 1 END) = 0
;
) 버튼을 클릭하여 작성 하시면 됩니다.