Oracle将行重复N次，其中N是列

本文介绍了Oracle将行重复N次，其中N是列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是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 |