데이터베이스 질문과 답변

혹시 차변대변 금액이 둘다 0인 내역을 어떻게 조회할 방법이 있을까요? 1 75

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을 걸어서 찾을라고 생각했는데 생각보다 잘안되네요

 

by jkson [2019.10.10 18:52:24]

group by 전표일자, 전표순번

having max(차변)=0 and max(대변)=0

by jkson [2019.10.11 09:45:55]
차변 대변
0 0
-200 -200

이런 값도 있을 수 있겠네요.

group by 전표일자, 전표순번

having max(abs(차변))=0 and max(abs(대변))=0

by 마농 [2019.10.11 09:22:48] 
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
;