create table qty_split
(
order varchar2(4),
article_code varchar2(4),
size_1 number(3,0),
size_2 number(3,0),
size_3 number(3,0)
);
insert into qty_split values ('a001', '1111', 123, 165, 85);
commit;
select * from qty_split;
'a001' '1111' 123 165 85
现在,我需要一个Oracle SQL查询以基于列size_1,size_2和size_3上的数量99的上限来拆分上面的行。
因此,预期结果将是:
'a001' '1111' 99 99 85
'a001' '1111' 24 66 0
你可以使用递归子查询分解子句将数据分割为任意数量:
WITH data ( "ORDER", article, size_1, size_2, size_3 ) AS (
SELECT * FROM qty_split
UNION ALL
SELECT "ORDER",
article,
GREATEST( size_1 - 99, 0 ),
GREATEST( size_2 - 99, 0 ),
GREATEST( size_3 - 99, 0 )
FROM data
WHERE size_1 > 99
OR size_2 > 99
OR size_3 > 99
)
SELECT "ORDER",
article,
LEAST( size_1, 99 ) AS size_1,
LEAST( size_2, 99 ) AS size_2,
LEAST( size_3, 99 ) AS size_3
FROM data
ORDER BY
"ORDER",
article,
ROWNUM;
其中,对于示例数据:
create table qty_split (
"ORDER" varchar2(4),
article_code varchar2(4),
size_1 number(3,0),
size_2 number(3,0),
size_3 number(3,0)
);
insert into qty_split
SELECT 'a001', '1111', 123, 165, 85 FROM DUAL UNION ALL
SELECT 'b002', '2222', 312, 45, 17 FROM DUAL UNION ALL
SELECT 'c003', '3333', 0, 0, 417 FROM DUAL;
输出:
订购| 文章| SIZE_1 | SIZE_2 | SIZE_3 :---- | :------ | -----:| -----:| -----: a001 | 1111 | 99 | 99 | 85 a001 | 1111 | 24 | 66 | 0 b002 | 2222 | 99 | 45 | 17 b002 | 2222 | 99 | 0 | 0 b002 | 2222 | 99 | 0 | 0 b002 | 2222 | 15 | 0 | 0 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 21岁
db <>在这里拨弄