데이터베이스 질문과 답변
원 데이타
| 20190901 | MD | 1 |
| 20190901 | AD | 2 |
| 20190902 | MD | 1 |
| 20190903 | MD | 2 |
| 20190904 | DD | 3 |
| 20190904 | MX | 1 |
| 20190905 | DD | 3 |
| 20190905 | MX | 2 |
| 20190906 | DD | 1 |
| 20190906 | MX | 2 |
원하는 data
| 20190901 | MD | 1 |
| 20190901 | AD | 2 |
| 20190902 | MD | 2 |
| 20190902 | AD | 2 |
| 20190903 | MD | 4 |
| 20190903 | AD | 2 |
| 20190904 | DD | 3 |
| 20190904 | MX | 1 |
| 20190904 | MD | 4 |
| 20190904 | AD | 2 |
| 20190905 | DD | 6 |
| 20190905 | MX | 3 |
| 20190905 | MD | 4 |
| 20190905 | AD | 2 |
| 20190906 | DD | 7 |
| 20190906 | MX | 5 |
| 20190906 | MD | 4 |
| 20190906 | AD | 2 |
SELECT B.PROD_DT, A.MODEL,
SUM(DISTINCT A.PROD ) AS S_PROD
FROM (
SELECT PROD_DT, MODEL,
PROD AS PROD
FROM T
WHERE PROD_DT = '201909'
) A,
(
SELECT PROD_DT, MODEL,
PROD AS PROD
FROM T
WHERE PROD_DT = '201909'
) B
WHERE A.PROD_DT <= B.PROD_DT
GROUP BY B.PROD_DT, A.MODEL
의 방법을 사용해 보았으나
SUM(DISTINCT A.PROD ) AS S_PROD 에서 동일한 값이 있을경우 중복이 제거 되는 상황이 발생 됩니다. ..
WITH DATA AS (
SELECT '20191101' DT,'MD' PROD,1 NO FROM DUAL UNION ALL
SELECT '20191101','AD',2 FROM DUAL UNION ALL
SELECT '20191102','MD',1 FROM DUAL UNION ALL
SELECT '20191103','MD',2 FROM DUAL UNION ALL
SELECT '20191104','DD',3 FROM DUAL UNION ALL
SELECT '20191104','MX',1 FROM DUAL UNION ALL
SELECT '20191105','DD',3 FROM DUAL UNION ALL
SELECT '20191105','MX',2 FROM DUAL UNION ALL
SELECT '20191106','DD',1 FROM DUAL UNION ALL
SELECT '20191106','MX',2 FROM DUAL
)
SELECT A.DT, B.PROD, SUM(B.NO)
FROM (SELECT TO_CHAR(SYSDATE,'YYYYMM')||LPAD(LEVEL,2,'0') DT
FROM DUAL
CONNECT BY LEVEL<=TO_CHAR(SYSDATE,'DD')) A
, DATA B
WHERE B.DT BETWEEN TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD') AND A.DT
AND B.DT BETWEEN TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD') AND TO_CHAR(SYSDATE,'YYYYMMDD')
GROUP BY A.DT, B.PROD
ORDER BY A.DT, B.PROD
;
존경합니다. ^^;;;
수정했습니다
AND B.DT BETWEEN TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD') AND TO_CHAR(SYSDATE,'YYYYMMDD')
이부분요;;
) 버튼을 클릭하여 작성 하시면 됩니다.