SQL: 결과 행을 여러 번 반복하고 행 번호를 지정합니다.

SQL: 결과 행을 여러 번 반복하고 행 번호를 지정합니다.

다음과 같은 결과를 가진 SQL 쿼리가 있습니다.

value | count
------+------
foo   |     1
bar   |     3
baz   |     2

이제 각 행이 다음과 같이 되도록 확장합니다.count1보다 큰 값이 여러 번 발생합니다.이 행들에도 번호를 매길 필요가 있습니다.그래서 저는 다음을 얻을 것입니다.

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

모든 주요 데이터베이스(Oracle, SQL Server, MySQL, Postgre)에서 이 작업을 수행해야 합니다.SQL 및 기타).따라서 서로 다른 데이터베이스에서 작동하는 솔루션이 이상적이지만, 모든 데이터베이스에서 작동할 수 있는 현명한 방법이 좋습니다.

숫자 표를 사용할 수 있습니다.

SELECT value, count, number
FROM table
    JOIN Numbers 
        ON table.count >= Numbers.number

다음은 MSSQL을 사용하는 SQLFiddle입니다.

MySQL의 경우 뷰를 통해 수행되는 poorman's generate_series를 사용합니다. MySQL은 빅4 중 CTE 기능이 없는 유일한 RDBMS입니다.

실제로 보기를 지원하는 데이터베이스에서 이 기술을 사용할 수 있습니다.이것이 사실상 모든 데이터베이스입니다.

제너레이터 기술 출처: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

SQL Server와 Oracle에는 왼쪽 이동 연산자가 없기 때문에 원래 기술의 비트와이즈(왼쪽 이동 및 비트와이즈 또는) 기술을 각각 단순한 곱셈과 추가로 대체하는 것이 유일한 수정 사항입니다.

이러한 추상화는 Oracle을 제외한 모든 데이터베이스에서 99% 작동합니다. Oracle의SELECT테이블 없이는 작동할 수 없습니다. 이를 위해서는 더미 테이블에서 선택해야 합니다. 오라클이 이미 제공한 테이블입니다.DUAL테이블. 데이터베이스 이동성은 파이프 드림입니다 :-)

다음은 비트 연산(이 시나리오에서는 실제로 필요하지 않음)과 기능 뉘앙스(제거)가 없는 모든 RDBMS에서 작동하는 추상화된 뷰입니다.OR REPLACE에CREATE VIEW모든 주요 데이터베이스 중 Postgresql 및 MySQL만 지원합니다.

Oracle 주의 사항:그냥 넣어주세요FROM DUAL각각의 뒤에SELECT표현

CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE VIEW generator_4k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE VIEW generator_64k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE VIEW generator_1m
AS SELECT ( ( hi.n * 65536 ) + lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

그런 다음 이 쿼리를 사용합니다.

SELECT t.value, t.cnt, i.n
FROM tbl t
JOIN generator_64k i 
ON i.n between 1 and t.cnt
order by t.value, i.n

Postgresql: http://www.sqlfiddle.com/ #!1/1541d/1

Oracle: http://www.sqlfiddle.com/ #!4/26c05/1

SQL Server: http://www.sqlfiddle.com/ #!6/84bee/1

MySQL: http://www.sqlfiddle.com/ #!2/78f5b/1

당신은 db-agnostic 솔루션을 요청했고 @Justin은 당신에게 좋은 솔루션을 주었습니다.
당신은 또한 요청했습니다.

그것을 어떤 데이터베이스에서도 작동시킬 수 있는 영리한 방법들

Postgre를 위한 것이 있습니다.SQL: 요청한 내용을 즉시 수행합니다.

SELECT val, ct, generate_series(1, ct) AS index
FROM   tbl;

그나저나, 나는 사용하지 않는 게 낫겠어요.value그리고.count열 이름으로.예약된 단어를 식별자로 사용하는 것은 나쁜 관행입니다.사용.val그리고.ct대신.

MySQL은 실제로 데이터베이스 세계의 IE이며 표준 및 기능과 관련하여 매우 중요합니다.

MySQL을 제외한 모든 주요 RDBMS에서 작동합니다.

with 
-- Please add this on Postgresql:
-- RECURSIVE
tbl_populate(value, cnt, ndx) as
(
  select value, cnt, 1 from tbl

  union all

  select t.value, t.cnt, tp.ndx + 1
  from tbl t
  join tbl_populate tp 
  on tp.value = t.value  
  and tp.ndx + 1 <= t.cnt
)
select * from tbl_populate
order by cnt, ndx

SQL Server: http://www.sqlfiddle.com/ #!6/sqla9/1

Oracle: http://www.sqlfiddle.com/ #!4/syscd/1

Postgresql: http://www.sqlfiddle.com/ #!1/0b03d/1

숫자 테이블 만들기 - 플랫폼에 따라 정의가 약간 다를 수 있습니다(SQL Server용).

CREATE TABLE Numbers(Number INT PRIMARY KEY);

INSERT Numbers 
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns;

이 temp는 SQL Server이기도 하지만 지정한 RDBMS 전체에서 유효해야 하는 조인 구문을 보여줍니다(비록 RDBMS를 사용하지 않기 때문에 테스트할 수 없음을 인정하지만).

DECLARE @foo TABLE(value VARCHAR(32), [count] INT);

INSERT @foo SELECT 'foo', 1
UNION ALL SELECT 'bar', 3
UNION ALL SELECT 'baz', 2;

SELECT f.value, f.[count], [index] = n.Number
FROM @foo AS f, Numbers AS n
WHERE n.Number <= f.[count];

결과(다시 SQL Server):

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

SQL Server 2005 이상에서는 이러한 작업을 반복적으로 처리할 수 있습니다.

declare @Stuff as Table ( Name VarChar(10), Number Int )
insert into @Stuff ( Name, Number ) values ( 'foo', 1 ), ( 'bar', 3 ), ( 'baz', 2 )

select * from @Stuff

; with Repeat ( Name, Number, Counter ) as (
  select Name, Number, 1
    from @Stuff
    where Number > 0
  union all
  select Name, Number, Counter + 1
    from Repeat
    where Counter < Number
  )
select *
  from Repeat
  order by Name, Counter -- Group by name.
  option ( maxrecursion 0 )

간하게에 의해.JOIN당신은 기록을 n번 반복하는 목표에 도달할 수 있습니다.
다음 쿼리는 각 레코드를 20회 반복합니다.

SELECT  TableName.*
FROM    TableName
JOIN    master.dbo.spt_values on type = 'P' and number < 20

에 대한 참고 사항master.dbo.spt_values on type = 'P':
는 이 다 음 조 건 에 의 하 코 사 데 니 됩 용 다 얻 는 숫 를 자 는 표 일 의련 된 딩 드 해 ▁of ▁which ▁is ▁in - 니 ▁this ▁of 다 ▁condition ▁a coded ▁series ▁number ▁getting ▁by▁it 이 ▁table ▁hard 됩 ▁is ▁used ▁for 용 는 사 표 음type='P'.

CTE를 사용할 수 있습니다.

WITH Numbers(Num) AS
(
    SELECT 1 AS Num
    UNION ALL 
    SELECT Num + 1
    FROM   Numbers c
    WHERE  c.Num < 1000
)

SELECT VALUE,COUNT, number
FROM   TABLE
       JOIN Numbers
            ON  TABLE.count >= Numbers.Num
OPTION(MAXRECURSION 1000)

을 조합해서 사용할 수 있습니다.LEVEL그리고.CROSS JOIN.

  SELECT *
    FROM yourtable
         CROSS JOIN (    SELECT ROWNUM index_t
                           FROM DUAL
                     CONNECT BY LEVEL <= (SELECT MAX (count_t) FROM yourtable))
   WHERE index_t <= count_t
ORDER BY VALUE, index_t;

데모

WITH Numbers(Num) AS(1을 Num Union으로 선택) 모든 Numbers c에서 Num + 1을 선택합니다(여기서 c).숫자 < 1000 )

TABLE JOIN Numbers on TABLE에서 VALUE, COUNT, Number FROM TABLE.count > = Numbers를 선택합니다.Num OPTION(최대 재귀 1000)

언급URL : https://stackoverflow.com/questions/10423767/sql-repeat-a-result-row-multiple-times-and-number-the-rows