Oracle将行重复N次，其中N是列 [英] Oracle duplicate row N times where N is a column

问题描述

我是Oracle的新手，我正在尝试做一些不寻常的事情.给定此表和数据，我需要选择每一行，并复制DupCount大于1的行.

I'm new to Oracle and I'm trying to do something a little unusual. Given this table and data I need to select each row, and duplicate ones where DupCount is greater than 1.

create table TestTable
(
  Name     VARCHAR(10),
  DupCount NUMBER
)

INSERT INTO TestTable VALUES ('Jane', 1);
INSERT INTO TestTable VALUES ('Mark', 2);
INSERT INTO TestTable VALUES ('Steve', 1);
INSERT INTO TestTable VALUES ('Jeff', 3);

所需结果:

Name        DupCount
---------   -----------
Jane        1
Mark        2
Mark        2
Steve       1
Jeff        3
Jeff        3
Jeff        3

如果通过单个select语句无法做到这一点，将非常感谢有关存储过程的帮助.

If this isn't possible via a single select statement any help with a stored procedure would be greatly appreciated.

推荐答案

您可以通过分层查询来做到这一点:

You can do it with a hierarchical query:

SQL小提琴

查询1 :

WITH levels AS (
  SELECT LEVEL AS lvl
  FROM   DUAL
  CONNECT BY LEVEL <= ( SELECT MAX( DupCount ) FROM TestTable )
)
SELECT Name,
       DupCount
FROM   TestTable
       INNER JOIN
       levels
       ON ( lvl <= DupCount )
ORDER BY Name

结果 :

Results:

|  NAME | DUPCOUNT |
|-------|----------|
|  Jane |        1 |
|  Jeff |        3 |
|  Jeff |        3 |
|  Jeff |        3 |
|  Mark |        2 |
|  Mark |        2 |
| Steve |        1 |

这篇关于Oracle将行重复N次，其中N是列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！