如何根据列的值来显示Oracle查询的结果 [英] How to unfold the results of an Oracle query based on the value of a column

问题描述

我在Oracle DB上有一个带有两列的表.我希望每行重复的次数与第二列中存储的次数相同.该表如下所示:

I have a table on a Oracle DB with two columns. I would like to see every row repeated as many times as the number stored in the second column. The table looks like this:

col1 col2  
a    2  
b    3  
c    1

我想编写一个返回此查询的查询:

I want to write a query that returns this:

col1 col2
a    2  
a    2   
b    3    
b    3    
b    3    
c    1

因此，col2中的值指示重复行的次数.有没有简单的方法可以做到这一点?

So the value from col2 dictates the number of times a row is repeated. Is there a simple way to achieve this?

谢谢！

推荐答案

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE test ( col1, col2 ) AS
          SELECT 'a', 2 FROM DUAL
UNION ALL SELECT 'b', 3 FROM DUAL
UNION ALL SELECT 'c', 1 FROM DUAL

查询1 :

SELECT col1,
       col2
FROM   test t,
       TABLE(
         CAST( 
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= t.col2
           )
           AS SYS.ODCINUMBERLIST
         )
       )

结果 :

Results:

| COL1 | COL2 |
|------|------|
|    a |    2 |
|    a |    2 |
|    b |    3 |
|    b |    3 |
|    b |    3 |
|    c |    1 |

这篇关于如何根据列的值来显示Oracle查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！