生成从 1 到特定值的数据

2021-09-10 00:00:00 tsql sql-server

我要生成这样的结果假设我有这些记录

I am going to generate a result like this suppose I have this records

Salah 3
John  2

我会期待这个

Salah
Salah
Salah
John
John

推荐答案

一种方法，如果您总是对 n 使用 small 值，则使用 rCTE:

One method, if you always have a small value for n is to use a rCTE:

WITH rCTE AS(
    SELECT [Name],
           N,
           1 AS I
    FROM dbo.YourTable
    UNION ALL
    SELECT [Name],
           N,
           I + 1
    FROM rCTE
    WHERE I < N)
SELECT [Name]
FROM rCTe
ORDER BY [Name] DESC;

如果您有更大的数字，请使用性能更好的 Tally.我在这里使用内联:

If you have much larger numbers, use a more performamt Tally. I use an inline here:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT --TOP (SELECT MAX(N) FROM dbo.YourTable) --Limits the number of rows, which could also provide a performance benefit if you only sometimes have large numbers
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --1000 rows, add more cross joins for more rows
SELECT [Name]
FROM dbo.YourTable YT
     JOIN Tally T ON YT.N >= T.I
ORDER BY YT.[Name] DESC;

db<>fiddle

相关文章