DROP TABLE TEST PURGE ;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;
WITH a AS (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= (SELECT MAX(t.quantity) FROM TEST t))SELECT t1.po_number,t1.product_name,1 quantity FROM TEST t1 LEFT JOIN a ON t1.quantity >= a.rn
SQL> WITH a AS (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= (SELECT MAX(t.quantity) FROM TEST t)) 2 SELECT t1.po_number,t1.product_name,1 quantity FROM TEST t1 LEFT JOIN a ON t1.quantity >= a.rn 3 ;PO_NUMBER PRODUCT_NAME QUANTITY---------------- ---------------------------------------------------------------- ----------CJ201300050 TP LINK无线路由器DAP-1353 1CJ201300050 TP LINK无线路由器DAP-1353 1CJ201300048 TP-LINK网络交换机TP-1024DT 1CJ201300048 TP-LINK网络交换机TP-1024DT 1CJ201300060 科密电子门禁MF850D 1CJ201300049 汉邦4路/特顿 600线半球/希捷硬盘500G 16 rows selectedSQL>
转载于:https://www.cnblogs.com/Uncho/p/4058809.html
相关资源：oracle字段分隔为多行显示