데이터베이스 질문과 답변
by 아니8083 [2020.02.25 18:07:17]
select a.TRF_BIZMN_ID, a.NON_TRANS_CNT, a.NON_TRANS_AMT, b.TRANS_CNT, b.TRANS_DC_AMT, b.DIFF_TRANS from
(SELECT TRF_BIZMN_ID, sum(USE_CNT) as NON_TRANS_CNT, sum(DEP_AMT) as NON_TRANS_AMT FROM MYBEE_DB.MYBEE_TOT_20190101
WHERE TRSCR_NUMT = 0
AND trim(GOFC_DTTI) ="" AND TRF_FILE_RCV_DT = '20190101'
group by TRF_BIZMN_ID ) a,
(select TRF_BIZMN_ID, sum(USE_CNT) as TRANS_CNT, sum(TRSCR_DSC_AMT) as TRANS_DC_AMT, sum(DEP_AMT) as DIFF_TRANS
from MYBEE_DB.MYBEE_TOT_20190101 where TRSCR_NUMT > 0 and trim(GOFC_DTTI) ="" AND TRF_FILE_RCV_DT = '20190101') b
where a.TRF_BIZMN_ID = b.TRF_BIZMN_ID;
원래는 실행 되어야 하는데,
In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'MYBEE_DB.MYBEE_TOT_20190101.TRF_BIZMN_ID'; this is incompatible with sql_mode=only_full_group_by
Error code: 1140
SQL state: 42000
이런 문구가 뜨면서 되지 않습니다...방법이 없을까요?
1. 오류 원인 : b 인라인뷰 안에 Group by 누락.
2. 표준 쿼리 사용 권장 : 쌍따옴표("") -> 홑따옴표('')
3. 쿼리 개선 : 테이블 두번 읽어 조인 -> 한번에 처리
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT trf_bizmn_id , SUM(CASE WHEN trscr_numt = 0 THEN use_cnt END) non_trans_cnt , SUM(CASE WHEN trscr_numt = 0 THEN dep_amt END) non_trans_amt , SUM(CASE WHEN trscr_numt > 0 THEN use_cnt END) trans_cnt , SUM(CASE WHEN trscr_numt > 0 THEN trscr_dsc_amt END) trans_dc_amt , SUM(CASE WHEN trscr_numt > 0 THEN dep_amt END) diff_trans FROM mybee_db.mybee_tot_20190101 WHERE trscr_numt >= 0 AND TRIM(gofc_dtti) = '' AND trf_file_rcv_dt = '20190101' GROUP BY trf_bizmn_id; |
) 버튼을 클릭하여 작성 하시면 됩니다.