sql-server - 如何使用交叉应用到多行？

Question

我有这张表:

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' ,2
    UNION ALL
    SELECT 'george' , 3
    UNION ALL
    SELECT 'ringo' , 1
)

我想显示每一行，Times次:

John 1
Paul 2
Paul 2
george 3
george 3
george 3
ringo 1

所以我知道如果我写Cross apply像:

SELECT *
FROM   cte
       CROSS APPLY(
        SELECT 1 AS ca
        UNION 
        SELECT 2
       ) y

然后每行将显示2次。

但我不想两次。我想要次次

问题

如何增强我的查询来做到这一点？

注:

我想到的一个非智能解决方案是创建一个 udf，为 n 参数创建 Times 行 - 然后在 Cross Apply 我只是这样做: select * from udf_toTable(Times))

Answer 1

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' , Times=2
    UNION ALL
    SELECT 'george' , Times=3
    UNION ALL
    SELECT 'ringo' , Times=1
),
multi as
(
    select 
        Name, Times, Times as num
    from cte
    union all
    select 
        Name, Times, num - 1
    from multi 
    where num > 1
)
select Name, Times from multi
order by Name

更新

没有递归

;WITH cte AS (
    SELECT Name='john' , Times=1    
    UNION ALL
    SELECT 'paul' , Times=2
    UNION ALL
    SELECT 'george' , Times=3
    UNION ALL
    SELECT 'ringo' , Times=1
)
select cte.*
from cte join 
    -- generate sequence of numbers 1,2 ... MAX(Times)
    (select top (select MAX(Times) from cte) ROW_NUMBER() over (order by object_id) rowNum from sys.objects) t
on cte.Times >= t.rowNum 
order by name