На носу зима, приближаются морозы, а это значит, что сегодня мы будем мариновать бананы. Для этого нам понадобятся следующие ингредиенты:
ID | INGREDIENT | MEASURE | QUANTITY |
---|---|---|---|
1 | Банан | Штука | 3 |
2 | Петрушка | Ветка | 2 |
3 | Вода | Литр | 3 |
4 | Соль | Ложка | 1 |
5 | Уксус | Ложка | 2 |
INGREDIENT | MEASURE | QUANTITY |
---|---|---|
Банан | Штука | 1 |
Банан | Штука | 1 |
Банан | Штука | 1 |
Петрушка | Ветка | 1 |
Петрушка | Ветка | 1 |
Вода | Литр | 1 |
Вода | Литр | 1 |
Вода | Литр | 1 |
Соль | Ложка | 1 |
Уксус | Ложка | 1 |
Уксус | Ложка | 1 |
create table bottle
as
with t (id, ingredient, measure, quantity) as (
select 1, 'Банан', 'Штука', 3 from dual union all
select 2, 'Петрушка', 'Ветка', 2 from dual union all
select 3, 'Вода', 'Литр', 3 from dual union all
select 4, 'Соль', 'Ложка', 1 from dual union all
select 5, 'Уксус', 'Ложка', 2 from dual
)
select * from t;
alter table bottle add primary key (id);
select b.ingredient, b.measure, 1 quantity
from bottle b, (
select level lvl
from dual
connect by level <= (select max(quantity) from bottle)) x
where b.quantity >= x.lvl
order by b.id
create table multiplier_rows as
select rownum as row_num
from dual
connect by level <= 10000;
alter table multiplier_rows add primary key (row_num);
select b.ingredient, b.measure, 1 quantity
from bottle b, multiplier_rows x
where b.quantity >= x.row_num
order by b.id
select ingredient, measure, 1 quantity
from bottle
connect by prior id = id
and prior dbms_random.value is not null
and level <= quantity
order by id
with boo (id, i, m, q) as (
select id, ingredient, measure, quantity
from bottle
union all
select id, i, m, q-1
from boo
where q > 1
)
select i ingredient, m measure, 1 quantity
from boo
order by id
select b.ingredient, b.measure, 1 quantity
from bottle b,
table(cast(multiset(select null
from dual
connect by level <= b.quantity)
as sys.odcinumberlist)) x
order by b.id
select b.ingredient, b.measure, 1 quantity
from bottle b,
table (select cast(collect(1) as sys.odcinumberlist)
from dual
connect by level <= b.quantity) x
order by b.id
select ingredient, measure, 1 quantity
from bottle
model
partition by (id, ingredient, measure, quantity)
dimension by (0 d)
measures(0 m)
rules iterate (10000) until m[iteration_number] = iteration_number (
m[iteration_number] = cv(quantity) - 1
)
order by id
select b.ingredient, b.measure, 1 quantity
from bottle b,
lateral(select null
from dual
connect by level <= b.quantity) x
order by b.id
drop table bottle;
drop table multiplier_rows;
К сожалению, не доступен сервер mySQL