SELECT ACCOUNTDATE, ACCOUNTINMNY AS accountInMny, ACCOUNTOUTMNY AS accountOutMny, (@NUM := @NUM + ACCOUNTINMNY - ACCOUNTOUTMNY) janak FROM se_account, (SELECT @NUM := 0) R WHERE 1 = 1 ORDER BY ACCOUNTDATE DESC limit 0, 10
안녕하세요.
해당쿼리 실행시 첨부파일의 결과로 나옵니다.
날짜로 정렬시 잔액부분이 거꾸로 계산되어 나오는데
잔액부분만 정렬을 바꿀수가 있을까요?
row 번호로 정렬하면 어떨까요?
with se_account as ( select '2020-08-27' ACCOUNTDATE, 500000 ACCOUNTINMNY, 0 ACCOUNTOUTMNY union all select '2020-08-31', 0 , 100000 union all select '2020-08-31', 200000, 0 union all select '2020-09-01', 100000, 0 union all select '2020-09-01', 100000, 0 union all select '2020-09-01', 300000, 0 union all select '2020-09-02', 0 , 50000 union all select '2020-09-03', 300000, 0 union all select '2020-09-03', 100000, 0 union all select '2020-09-05', 20000 , 0 union all select '2020-09-22', 0 , 50000 union all select '2020-09-22', 100000, 0 ) SELECT * FROM ( SELECT ACCOUNTDATE, ACCOUNTINMNY AS accountInMny, ACCOUNTOUTMNY AS accountOutMny, (@NUM := @NUM + ACCOUNTINMNY - ACCOUNTOUTMNY) janak, (@RNUM := @RNUM + 1) rownum FROM se_account, (SELECT @NUM := 0) R, (SELECT @RNUM := 0) RN WHERE 1 = 1) t ORDER BY rownum DESC limit 0, 10
기존 자료가 일자로 정렬되어 있다면 위와 같이 가능합니다.
하지만 일자로 정렬이 되어 있지 않은 상태라면?
인라인뷰를 이용해 두번에 나누어 정렬을 하셔야 합니다.
만약 분석함수 사용이 가능한 버전이라면 분석함수를 이용하세요.
http://gurubee.net/article/83350
SELECT * FROM (SELECT ACCOUNTDATE , ACCOUNTINMNY AS accountInMny , ACCOUNTOUTMNY AS accountOutMny , (@num := @num + accountinmny - accountoutmny) janak FROM se_account , (SELECT @num := 0) r WHERE 1 = 1 ORDER BY accountdate ) a ORDER BY accountdate DESC LIMIT 0, 10 ;
SELECT ACCOUNTDATE , ACCOUNTINMNY AS accountInMny , ACCOUNTOUTMNY AS accountOutMny , SUM(accountinmny - accountoutmny) OVER(ORDER BY accountdate) janak FROM se_account WHERE 1 = 1 ORDER BY accountdate DESC LIMIT 0, 10 ;