Oracle根据数字列一行拆多行

需求:

数据库中存储了各个资产的数量,比如椅子2,电脑3,系统有导出的功能,之前的功能是一条资产导出一条记录,现在要求根据数量导出,数量字段存储的是多少就导出多少条.

数据示例:

 目标结果:

 sql:

SELECT distinct id,name,"number",level
from TEST_TEMP t
connect by level <= t."number"
order by id;

测试数据:

create table TEST_TEMP
(
    ID       NUMBER,
    "number" NUMBER(18, 4),
    NAME     VARCHAR2(32)
);

INSERT INTO XZZC.TEST_TEMP (ID, "number", NAME) VALUES (1, 2.0000, '椅子');
INSERT INTO XZZC.TEST_TEMP (ID, "number", NAME) VALUES (2, 3.0000, '电脑');
INSERT INTO XZZC.TEST_TEMP (ID, "number", NAME) VALUES (3, 3.0000, '鼠标');
INSERT INTO XZZC.TEST_TEMP (ID, "number", NAME) VALUES (4, 4.0000, '键盘');