多次选择同一行 [英] Selecting the same row multiple times

问题描述

我有一个表，其中包含主对象的一些子项.任何孩子都可以出现多次，并且有一个包含该数字的 Occurences 列，因此表中的数据类似于:

I have a table that has some children of a master object. Any child can occur more than once, and there is a Occurences column that contains that number, so the data in the table is something like:

ChildID | ParentID | Occurences
-------------------------------
      1 |        1 |        2
      2 |        1 |        2
      3 |        2 |        1
      4 |        2 |        3

我需要得到所有孩子的列表，每个孩子在结果中出现的正确次数，比如

I need to get a list of all the children, with each child appearing the corect number of times in the result, something like

IDENT | ChildID | ParentID
--------------------------
    1 |       1 |        1
    2 |       1 |        1
    3 |       2 |        1
    4 |       2 |        1
    5 |       3 |        2
    6 |       4 |        2
    7 |       4 |        2
    8 |       4 |        2

我可以使用一个游标循环表并根据需要插入尽可能多的行，但我认为这不是最好的解决方案.

I can do this with a cursor that loops the table and inserts as many rows as neccessary, but I don't think that that is the best solution possible.

感谢您的帮助

包含创建脚本:

DECLARE @Children TABLE (ChildID int, ParentID int, Occurences int)

INSERT  @Children
SELECT  1, 1, 2 UNION ALL
SELECT  2, 1, 2 UNION ALL
SELECT  3, 2, 1 UNION ALL
SELECT  4, 2, 3

推荐答案

;with C as
(
  select ChildID,
         ParentID,
         Occurences - 1 as Occurences
  from @Children
  union all
  select ChildID,
         ParentID,
         Occurences - 1 as Occurences
  from C
  where Occurences > 0
)
select row_number() over(order by ChildID) as IDENT,
       ChildID,
       ParentID
from C
order by IDENT

这篇关于多次选择同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！