根据存储值乘以可能的选项返回多行

Question

我正在尝试返回多行信息，这些信息以后可以进行排名。我有一个产品表 (PRODUCT(，其中存储了 product_id (PRODUCT_ID( 和单位重量 (PROD_UNIT_WGT(，我想返回可能的包装数量，比如 1,2,3,4,5,6,7,8 等，最多 310。如果没有309个工会，这可能吗？

回报应类似于

select PRODUCT_ID, PROD_UNIT_WGT, 1 as QUANTITY, PROD_UNIT_WGT * 1 as TOTAL_WGT
from PRODUCT
where PRODUCT_ID = PROD_A
union
select PRODUCT_ID, PROD_UNIT_WGT, 2 as QUANTITY, PROD_UNIT_WGT * 2 as TOTAL_WGT
from PRODUCT
where PRODUCT_ID = PROD_A
union
select PRODUCT_ID, PROD_UNIT_WGT, 3 as QUANTITY, PROD_UNIT_WGT * 3 as TOTAL_WGT
from PRODUCT
where PRODUCT_ID = PROD_A

提前感谢您的帮助！

Answer 1

创建表或临时表并遍历您的产品。下面的代码示例，我通过Oracle Live SQL完成了此操作：

create global temporary table product(
product_id NUMBER primary key,
prod_unit_wgt NUMBER)
on commit preserve rows;
create global temporary table temp_product(
product_id NUMBER,
prod_unit_wgt NUMBER,
quantity NUMBER,
total_wgt NUMBER,
constraint pk_temp_product primary key (product_id, quantity))
on commit preserve rows;
declare
cursor product_cur is
select product_id, prod_unit_wgt
from product;
begin
insert into product values(1, 3);
for rec in product_cur loop
for i in 1..310 loop
insert into temp_product(product_id, prod_unit_wgt, quantity, total_wgt)
values ( rec.product_id, rec.prod_unit_wgt, i, rec.prod_unit_wgt * i);
end loop;
end loop;
end;
select * from temp_product;
PRODUCT_ID  PROD_UNIT_WGT   QUANTITY    TOTAL_WGT
1   3   1   3
1   3   2   6
1   3   3   9
1   3   4   12

Answer 2

您可以尝试使用伪列ROWNUM。

select PRODUCT_ID, PROD_UNIT_WGT, rownum as QUANTITY, PROD_UNIT_WGT * rownum as TOTAL_WGT
from PRODUCT
where PRODUCT_ID = PROD_A
and rownum < 311

Answer 3

编写为您编写查询的查询怎么样？为了更好的可读性，我将其限制为 3(请参阅第 #9 行中的connect by子句(;你可以使用任何你想要的数字。

SQL> with data as
2    (select 'select product_id, ' || chr(10) ||
3            '  prod_unit_wgt, '   || level || ' as quantity, ' || chr(10) ||
4            '  prod_unit_wgt * '  || level || ' as total_wgt ' || chr(10) ||
5            'from PRODUCT where PRODUCT_ID = PROD_A '          || chr(10) ||
6            'union all ' val,
7            level lvl
8     from dual
9     connect by level <= 3
10    )
11  select rtrim (
12                xmlagg (xmlelement (e, val || chr(10)) order by lvl).extract (
13                   '//text()'),
14                chr(10) || 'union all ')
15                result
16  from data;
RESULT
--------------------------------------------------------------------------------
select product_id,
prod_unit_wgt, 1 as quantity,
prod_unit_wgt * 1 as total_wgt
from PRODUCT where PRODUCT_ID = PROD_A
union all
select product_id,
prod_unit_wgt, 2 as quantity,
prod_unit_wgt * 2 as total_wgt
from PRODUCT where PRODUCT_ID = PROD_A
union all
select product_id,
prod_unit_wgt, 3 as quantity,
prod_unit_wgt * 3 as total_wgt
from PRODUCT where PRODUCT_ID = PROD_A

SQL>