oracle 将一行拆分成多行


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 1
CJ201300050 TP LINK无线路由器DAP-1353 1
CJ201300048 TP-LINK网络交换机TP-1024DT 1
CJ201300048 TP-LINK网络交换机TP-1024DT 1
CJ201300060 科密电子门禁MF850D 1
CJ201300049 汉邦4路/特顿 600线半球/希捷硬盘500G 1

6 rows selected

SQL>

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。