Oracle Connect by 單行變多行

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2126845/，如需轉載，請註明出處，否則將追究法律責任。