데이터베이스 질문과 답변
by 달구지 [2009.04.24 09:48:09]
WITH t AS
(SELECT '20090101' dt_jojak
, '20090101' dt_confirm
, '20090101' sil_dt_georae
, 100 sell
FROM DUAL
UNION ALL
SELECT '20090102' dt_jojak
, '20090101' dt_confirm
, '20090101' sil_dt_georae
, 100 sell
FROM DUAL
UNION ALL
SELECT '20090103' dt_jojak
, '20090101' dt_confirm
, '20090102' sil_dt_georae
, 100 sell
FROM DUAL
UNION ALL
SELECT '20090103' dt_jojak
, '20090101' dt_confirm
, '20090102' sil_dt_georae
, 100 sell
FROM DUAL)
SELECT dt_gbn
, dt
, amtsum
, SUM(amtsum) OVER(PARTITION BY dt_gbn ORDER BY dt ASC)
FROM (SELECT '1' AS dt_gbn
, dt_jojak AS dt
, SUM(sell) AS amtsum
FROM t
GROUP BY dt_jojak
UNION ALL
SELECT '2' AS dt_gbn
, dt_confirm AS dt
, SUM(sell) AS amtsum
FROM t
GROUP BY dt_confirm
UNION ALL
SELECT '3' AS dt_gbn
, sil_dt_georae AS dt
, SUM(sell) AS amtsum
FROM t
GROUP BY sil_dt_georae)
ORDER BY dt_gbn
, dt
이렇게 하면 dt_gbn 별로 잔액을 보여주기는 하는데
만약 dt_gbn 별로 데이터가 20081231 일자로 1000, 2000, 3000 이 있다면 더해서 보여주고 싶은데 안되네요
도움좀 부탁 드려요
제가 생각하는 것과 같은건지 모르겠네요
WITH t AS
(SELECT '20090101' dt_jojak , '20090101' dt_confirm , '20090101' sil_dt_georae, 100 sell FROM DUAL
UNION ALL SELECT '20090102', '20090101', '20090101', 100 FROM DUAL
UNION ALL SELECT '20090103', '20090101', '20090102', 100 FROM DUAL
UNION ALL SELECT '20090103', '20090101', '20090102', 100 FROM DUAL
UNION ALL SELECT '20081231', '20081231', '20081231', 1000 FROM DUAL)
SELECT dt_gbn,
dt,
amtsum,
Decode(dt,'20081231',SUM(amtsum) OVER(PARTITION BY dt),SUM(amtsum) OVER(PARTITION BY dt ORDER BY rownum ASC))
FROM
(SELECT Decode(dt_jojak||dt_confirm||sil_dt_georae,dt_jojak,1,dt_confirm,2,sil_dt_georae,3) dt_gbn,
Greatest(Nvl(dt_jojak,0),Nvl(dt_confirm,0),Nvl(sil_dt_georae,0)) dt,
Sum(sell) amtsum
FROM t
GROUP BY Grouping sets((dt_jojak),(dt_confirm),(sil_dt_georae)))
ORDER BY dt_gbn
, dt
쿼리가 길어지지 않게
UNION ALL을 ?馨? 만들었습니다.
신규 Row Insert 하기전에 최종잔액 + 신규 Row 를 하고 싶다구요
(SELECT '20090101' dt_jojak , '20090101' dt_confirm , '20090101' sil_dt_georae, 100 sell FROM DUAL
UNION ALL SELECT '20090102', '20090101', '20090101', 100 FROM DUAL
UNION ALL SELECT '20090103', '20090101', '20090102', 100 FROM DUAL
UNION ALL SELECT '20090103', '20090101', '20090102', 100 FROM DUAL
UNION ALL SELECT '20081231', '20081231', '20081231', 1000 FROM DUAL)
SELECT dt_gbn,
dt,
amtsum,
SUM(amtsum) OVER(PARTITION BY dt)
FROM
(SELECT Decode(dt_jojak||dt_confirm||sil_dt_georae,dt_jojak,1,dt_confirm,2,sil_dt_georae,3) dt_gbn,
Greatest(Nvl(dt_jojak,0),Nvl(dt_confirm,0),Nvl(sil_dt_georae,0)) dt,
Sum(sell) amtsum
FROM t
GROUP BY Grouping sets((dt_jojak),(dt_confirm),(sil_dt_georae)))
WHERE dt <= '20090101'
ORDER BY dt_gbn
, dt
최종잔액이면 이걸 말씀하시는것 같네요
) 버튼을 클릭하여 작성 하시면 됩니다.