嗨，我不确定这是否在oracle数据库中或任何一个数据库中都可能，但有可能做到这一点：
我有什么：
Document | Volume | BAC | CO
-----------|-----------|---------|---------
TA1 | 4 | 2 | 0
我想要什么：
Document | Volume | BAC | CO | ID
-----------|-----------|---------|---------|---------
TA1 | 1 | 0 | 0 | 1
TA1 | 1 | 0 | 0 | 2
TA1 | 1 | 0 | 0 | 3
TA1 | 1 | 0 | 0 | 4
TA1 | 0 | 1 | 0 | 5
TA1 | 0 | 1 | 0 | 6
我试过使用WITH，但我的Sqldevelopper现在一团糟，因为我知道WITH不能使用两次，也不能在UNION中使用。
PS：行数需要等于(Volume+Bac+CO(。
在ORACLE 12中可以执行此操作吗？
这应该可以工作，并且只对数据进行一次检查。这是分层查询的一个简单应用程序。
我添加了更多的测试数据；注意，在TA3的情况下，输出中不应该有行(因为行中的三个值都是0(。
with
test_data ( document, volume, bac, co ) as (
select 'TA1', 4, 2, 0 from dual union all
select 'TA2', 0, 0, 1 from dual union all
select 'TA5', 0, 0, 0 from dual
)
-- end of test data; actual solution (SQL query) begins below this line
select document,
case when level <= volume then 1 else 0 end as volume,
case when level > volume and level <= volume + bac then 1 else 0 end as bac,
case when level > volume + bac then 1 else 0 end as co,
level as id
from test_data
where volume + bac + co > 0
connect by level <= volume + bac + co
and prior document = document
and prior sys_guid() is not null
order by document, id -- ORDER BY is optional
;
DOC VOLUME BAC CO ID
--- ---------- ---------- ---------- ----------
TA1 1 0 0 1
TA1 1 0 0 2
TA1 1 0 0 3
TA1 1 0 0 4
TA1 0 1 0 5
TA1 0 1 0 6
TA2 0 0 1 1
7 rows selected
您可以尝试这个->首先，创建一个临时派生表，其中包含的行数等于允许的最大行数：
CREATE TABLE TMP_TABLE AS
SELECT s.num_col,ROW_NUMBER() OVER(ORDER BY 1) as rnk
FROM (
SELECT 1 as num_col FROM dual
UNION ALL SELECT 1 as num_col FROM dual
UNION ALL SELECT 1 as num_col FROM dual
UNION ALL SELECT 1 as num_col FROM dual
...... As many necessary) s
然后，使用这个：
SELECT p.num_col as volume,0 as BAC,0 as CO
FROM TMP_TABLE p
JOIN YourTable t
ON(t.Volume >= p.rnk)
UNION ALL
SELECT 0 as volume,p.num_col as BAC,0 as CO
FROM TMP_TABLE p
JOIN YourTable t
ON(t.BAC >= p.rnk)
UNION ALL
SELECT 0 as volume,0 as BAC,p.num_col as CO
FROM TMP_TABLE p
JOIN YourTable t
ON(t.CO >= p.rnk)
select t.document
,decode(t.col,'V',1,0) as volume
,decode(t.col,'B',1,0) as bac
,decode(t.col,'C',1,0) as co
,row_number () over
(
partition by t.document order by decode(t.col,'V',1,'B',2,'C',3)
) as id
from t unpivot (n for col in (volume as 'V',bac as 'B',co as 'C')) t
join (select level as n from dual connect by level <= (select max(greatest(volume,bac,co)) from t)) c
on c.n <= t.n
;
或
select t.document
,decode(t.col,'V',1,0) as volume
,decode(t.col,'B',1,0) as bac
,decode(t.col,'C',1,0) as co
,t.pre + c.n as id
from (select t.*,0 as pre_v,volume as pre_b,volume+bac as pre_c from t) t
unpivot ((n,pre) for col in ((volume,pre_v) as 'V',(bac,pre_b) as 'B',(co,pre_c) as 'C')) t
join (select level as n from dual connect by level <= (select max(greatest(volume,bac,co)) from t)) c
on c.n <= t.n
order by 1,id
;
或
with r (col,Document,col_val,n,id) as
(
select c.col,t.Document,decode (c.col,1,t.Volume,2,t.BAC,3,t.CO),1,decode (c.col,1,1,2,t.Volume+1,3,t.Volume+t.BAC+1)
from t cross join (select level as col from dual connect by level <= 3) c
where decode (c.col,1,t.Volume,2,t.BAC,3,t.CO) > 0
union all
select r.col,r.Document,r.col_val,r.n+1,r.id+1
from r join (select level as col from dual connect by level <= 3) c
on c.col = r.col and r.n < r.col_val
)
select Document
,decode (col,1,1,0) as Volume
,decode (col,2,1,0) as BAC
,decode (col,3,1,0) as CO
,id
from r
order by 1,5
;
或
with r_Volume (Document,col_val,Volume,Bac,Co,n) as (select Document,Volume,1,0,0,1 from t where Volume > 0 union all select Document,col_val,Volume,Bac,Co,n+1 from r_Volume where n < col_val)
,r_Bac (Document,col_val,Volume,Bac,Co,n) as (select Document,Bac ,0,1,0,1 from t where Bac > 0 union all select Document,col_val,Volume,Bac,Co,n+1 from r_Bac where n < col_val)
,r_Co (Document,col_val,Volume,Bac,Co,n) as (select Document,Co ,0,0,1,1 from t where Co > 0 union all select Document,col_val,Volume,Bac,Co,n+1 from r_Co where n < col_val)
select Document,Volume,Bac,Co,row_number () over (partition by Document order by Volume desc,Bac desc,Co desc) as id
from ( select Document,Volume,Bac,Co from r_Volume
union all select Document,Volume,Bac,Co from r_Bac
union all select Document,Volume,Bac,Co from r_Co
) r
;
以下是解决此问题的另一种方法：
WITH sample_data( document, volume, bac, co ) AS (
SELECT 'TA1', 4, 2, 0
FROM dual
)
, recursive( document, col_id, col_cnt, id ) AS (
SELECT document -- First unpivot the data for each document
, col_id
, col_cnt
, SUM( col_cnt ) over( partition BY document order by col_id ) - col_cnt + 1
FROM sample_data UNPIVOT( col_cnt FOR col_id IN( volume AS 1,
bac AS 2,
co AS 3 ) )
WHERE col_cnt > 0 -- But throw away rows with zero col_cnts.
UNION ALL
SELECT document
, col_id
, col_cnt - 1 -- Recursively decrement the col_cnt
, id + 1 -- and increment id
FROM recursive
WHERE col_cnt > 1 -- until col_cnt is no longer > 1
)
SELECT document -- Finally pivot the recursive data
, volume -- back to its original columns
, bac
, co
, id
FROM recursive PIVOT( COUNT( * ) FOR col_id IN( 1 AS volume, 2 AS bac, 3 AS co ) )
ORDER BY document
, id;