데이터베이스 질문과 답변
SELECT
A.id,
A.val,
SUM(a.val) OVER(ORDER BY A.id ROWS UNBOUNDED PRECEDING) AS total
FROM (
SELECT 1 AS ID, 68 AS val FROM dual
UNION ALL
SELECT 2 AS ID, 100 AS val FROM dual
UNION ALL
SELECT 3 AS ID, 5 AS val FROM dual
) A
ID VAL TOTAL
1 68 68
2 100 168
3 5 173
Total 조건이 70이면
ID VAL TOTAL
1 68 68
2 100 168
여기까지만 조회 되게 할 수 있는 방법이 있을까요?
사용할 용도는 사용할 재고가 70개 인데 팔렛트 우선순위로 68, 168, 5개 있는데 이중에서 70개만 빼와야 해서 두줄만 보여 줘야 합니다.
계속 누적만 되는 건가요? 재고가 줄어드는 일은 없는지..
누적만 되는 상황이라면 간단하게
SELECT ID, VAL, TOTAL FROM (SELECT A.ID, A.VAL, SUM(A.VAL) OVER (ORDER BY A.ID) AS TOTAL FROM (SELECT 1 AS ID, 68 AS VAL FROM DUAL UNION ALL SELECT 2 AS ID, 100 AS VAL FROM DUAL UNION ALL SELECT 3 AS ID, 5 AS VAL FROM DUAL) A ) WHERE TOTAL <= 70 OR 70 BETWEEN TOTAL - VAL AND TOTAL
SELECT ID, VAL, TOTAL
FROM (
SELECT A.ID, A.VAL, SUM (A.VAL) OVER (ORDER BY A.ID) AS TOTAL
FROM (SELECT 1 AS ID, 68 AS VAL FROM DUAL
UNION ALL
SELECT 2 AS ID, 100 AS VAL FROM DUAL
UNION ALL
SELECT 3 AS ID, 5 AS VAL FROM DUAL
) A
)
WHERE TOTAL - VAL < 68
감사합니다. 나이 먹으니 산수가 안되네요. ㅎㅎ 부등호를 < 만 하면 될거 같습니다.
아..저도 산수를 못해서;;
) 버튼을 클릭하여 작성 하시면 됩니다.