create table my_t1(
rowno number,
wareid number,
maxqty number,
wareqty number
)
insert into my_t1 values(1, 100, 400, 500);
insert into my_t1 values(2, 110, 240, 600);
SELECT wareqty,
maxqty,
wareid,
rowno,
CASE
WHEN leijia > wareqty THEN
wareqty -
nvl(lag(leijia) over(PARTITION BY wareid, rowno ORDER BY lv), 0)
ELSE
maxqty
END allocqty
FROM (SELECT x.*,
LEVEL lv,
SUM(maxqty) over(PARTITION BY wareid, rowno ORDER BY LEVEL) leijia
FROM (SELECT ceil(wareqty / maxqty) num_rows,
wareqty,
maxqty,
wareid,
rowno,
SUM(ceil(wareqty / maxqty)) over(ORDER BY rownum) - rownum + 1 psum
FROM my_t1) x
CONNECT BY rownum <= psum
ORDER BY wareid, lv) t
SQL 2:
SELECT wareqty,
maxqty,
wareid,
rowno,
CASE
WHEN leijia > wareqty THEN
wareqty -
nvl(lag(leijia) over(PARTITION BY wareid, rowno ORDER BY lv), 0)
ELSE
maxqty
END allocqty
FROM (SELECT x.*,
LEVEL lv,
SUM(maxqty) over(PARTITION BY wareid, rowno ORDER BY LEVEL) leijia
FROM (SELECT ceil(wareqty / maxqty) num_rows,
wareqty,
maxqty,
wareid,
rowno,
SUM(ceil(wareqty / maxqty)) over(ORDER BY rownum) - rownum + 1 psum
FROM my_t1) x
CONNECT BY rownum <= psum
ORDER BY wareid, lv) t
rowno number,
wareid number,
maxqty number,
wareqty number
)
insert into my_t1 values(1, 100, 400, 500);
insert into my_t1 values(2, 110, 240, 600);
maxqty,
wareid,
rowno,
CASE
WHEN leijia > wareqty THEN
wareqty -
nvl(lag(leijia) over(PARTITION BY wareid, rowno ORDER BY lv), 0)
ELSE
maxqty
END allocqty
FROM (SELECT x.*,
LEVEL lv,
SUM(maxqty) over(PARTITION BY wareid, rowno ORDER BY LEVEL) leijia
FROM (SELECT ceil(wareqty / maxqty) num_rows,
wareqty,
maxqty,
wareid,
rowno,
SUM(ceil(wareqty / maxqty)) over(ORDER BY rownum) - rownum + 1 psum
FROM my_t1) x
CONNECT BY rownum <= psum
ORDER BY wareid, lv) t
SQL 2:
SELECT wareqty,
maxqty,
wareid,
rowno,
CASE
WHEN leijia > wareqty THEN
wareqty -
nvl(lag(leijia) over(PARTITION BY wareid, rowno ORDER BY lv), 0)
ELSE
maxqty
END allocqty
FROM (SELECT x.*,
LEVEL lv,
SUM(maxqty) over(PARTITION BY wareid, rowno ORDER BY LEVEL) leijia
FROM (SELECT ceil(wareqty / maxqty) num_rows,
wareqty,
maxqty,
wareid,
rowno,
SUM(ceil(wareqty / maxqty)) over(ORDER BY rownum) - rownum + 1 psum
FROM my_t1) x
CONNECT BY rownum <= psum
ORDER BY wareid, lv) t
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-2126845/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-2126845/