看到ITPUB上的一个案例来源
CREATE TABLE TEST (po_number VARCHAR2(16),product_name VARCHAR2(64),quantity NUMBER);
INSERT INTO TEST VALUES('CJ201300048','TP-LINK网络交换机TP-1024DT ', 2);
INSERT INTO TEST VALUES('CJ201300049','汉邦4路/特顿 600线半球/希捷硬盘500G',1);
INSERT INTO TEST VALUES('CJ201300050','TP LINK无线路由器DAP-1353',2);
INSERT INTO TEST VALUES('CJ201300060','科密电子门禁MF850D',1);
commit;
<pre name="code" class="sql">SQL> select * from test;
PO_NUMBER PRODUCT_NAME QUANTITY
---------------- ---------------------------------------------------------------- ----------
CJ201300048 TP-LINK网络交换机TP-1024DT 2
CJ201300049 汉邦4路/特顿 600线半球/希捷硬盘500G 1
CJ201300050 TP LINK无线路由器DAP-1353 2
CJ201300060 科密电子门禁MF850D 1
<pre name="code" class="sql">SQL> WITH t AS (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=(SELECT MAX(quantity) FROM TEST))
2 SELECT TEST.po_number,test.product_name,1 quantity
3 FROM TEST LEFT JOIN t ON test.quantity>=t.rn
4 order by test.po_number;
PO_NUMBER PRODUCT_NAME QUANTITY
---------------- ---------------------------------------------------------------- ----------
CJ201300048 TP-LINK网络交换机TP-1024DT 1
CJ201300048 TP-LINK网络交换机TP-1024DT 1
CJ201300049 汉邦4路/特顿 600线半球/希捷硬盘500G 1
CJ201300050 TP LINK无线路由器DAP-1353 1
CJ201300050 TP LINK无线路由器DAP-1353 1
CJ201300060 科密电子门禁MF850D 1
6 rows selected.