데이터베이스 질문과 답변
with t as
(
select '2017' AS YEAR_YY , '20171107'AS EXAM_YMD, '2017000396' AS EXAM_NO,'31' AS EXAM_GB , '3311' AS EXAM_DTL_GB ,'0000001000'AS MAIN_KEY ,'80710' AS TOT_AMT ,'0' AS BZPLC_AMT ,'72639' AS HINSU_AMT from dual
union all
select '2017', '20171107', '2017000396', '31', '3312', '0000001000', '11990', '0','10791' from dual
union all
SELECT '2017', '20171110', '2017000396', '31', '3112', '0000001000', '61390', '0' ,'55251' from dual
union all
SELECT '2017', '20171110', '2017000396', '31', '3601', '0000001000', '6370', '0' ,'5733' from dual
union all
SELECT '2017', '20171107', '2017000396', '31', '3601', '0000001000', '6370', '0' ,'5733' from dual
union all
SELECT '2017', '20171113', '2017000396', '31', '3121', '0000001000', '54280', '0', '48852'from dual
)
select YEAR_YY
, EXAM_NO
, EXAM_YMD
, SUM(sb.TOT_AMT ) TOT_AMT --총금액
, SUM(sb.BZPLC_AMT ) BZPLC_AMT -- 계약처
, SUM(sb.HINSU_AMT ) HINSU_AMT -- 건강보험
from t sb
where exam_gb in ('31','32')
and exam_no = '2017000396'
group by exam_gb,exam_ymd,EXAM_NO,YEAR_YY
------------------------------------------------결과값 2017 2017000396 20171110 67760 0 60984 2017 2017000396 20171107 99070 0 89163 2017 2017000396 20171113 54280 0 48852 원하는 값
EXAM_YMD 20171113에 있는 EXAM_DTL_GB 3121의 금액이
20171110금액에 sum이 되게 하고싶습니다.
2017 2017000396 20171110 122040 0 109836 2017 2017000396 20171107 99070 0 8916
두개를 연결시킬 고리가 substr()EXAM_DTL_GB,0,2,)로해서 앞에 두자가 같으면 같다고 보면되는데 쉽지가않네요..
by 강재민
[2017.12.13 15:40:10]
group by 절에 YEAR_YY 이걸 빼고 select문에서 YEAR_YY 이걸 min 또는 max 함수 사용하면 되는거 아닌가요?
by 우리집아찌
[2017.12.13 16:00:12]
앞의 두자리가 다른 로우에 같은게 발생하면 어쩌나요?
) 버튼을 클릭하여 작성 하시면 됩니다.