Запрос SQL Server на основе столбца для возвращаемых строк

Вопрос или проблема

INSERT [dbo].[Table1] (InvoiceID, CompanyName, RowsNeeded) VALUES ('A1000', 'Компания 1', 3)
INSERT [dbo].[Table1] (InvoiceID, CompanyName, RowsNeeded) VALUES ('A1001', 'Компания 2', 2)

'A1000','Компания 1'
'A1000','Компания 1'
'A1000','Компания 1'
'A1001','Компания 2'
'A1001','Компания 2'

WITH cte (InvoiceID, CompanyName, RowsNeeded, level) AS  (
    SELECT InvoiceID, CompanyName, RowsNeeded, 1 AS level
    FROM Table1

    UNION ALL

    SELECT t2.InvoiceID, t2.CompanyName, t2.RowsNeeded, t2.level + 1
    FROM Table1 t1
    INNER JOIN cte t2
        ON t2.InvoiceID = t1.InvoiceID AND
           t1.level + 1 <= t1.RowsNeeded
)

SELECT InvoiceID, CompanyName
FROM cte
ORDER BY InvoiceID;

with cte as (
  select InvoiceID, CompanyName, RowsNeeded, 1 as rn
  from [dbo].[Table1]
  union all
  select InvoiceID, CompanyName, RowsNeeded, rn + 1 as rn 
  from cte 
  where rn < RowsNeeded
)

select InvoiceID, CompanyName 
from cte
order by InvoiceID, CompanyName

WITH RecursiveCTE AS (
  SELECT InvoiceID, CompanyName, RowsNeeded, 1 AS RowNumber
  FROM Table1
  UNION ALL
  SELECT t.InvoiceID, t.CompanyName, t.RowsNeeded, RowNumber + 1
  FROM RecursiveCTE r
  JOIN Table1 t ON r.InvoiceID = t.InvoiceID
  WHERE RowNumber < t.RowsNeeded
)

SELECT InvoiceID, CompanyName
FROM RecursiveCTE
ORDER BY InvoiceID
OPTION (MAXRECURSION 0);

Ответ или решение

Чтобы создать SQL-запрос, который позволяет вернуть определенное количество строк на основе значений в третьем столбце таблицы, можно использовать рекурсивный обобщенный табличный выражение (CTE) в SQL Server. Ваша задача заключается в репликации записей, основанных на значении столбца RowsNeeded, который указывает, сколько строк вы хотите вернуть для каждой записи.

Вот пример, который демонстрирует, как это сделать на основе вашей таблицы Table1:

WITH RecursiveCTE AS (
    -- Начальный запрос: выбираем строки из основной таблицы с начальным уровнем (RowNumber) равным 1
    SELECT InvoiceID, CompanyName, RowsNeeded, 1 AS RowNumber
    FROM Table1

    UNION ALL

    -- Рекурсивный запрос: добавляем повторные строки, увеличивая счетчик (RowNumber) на 1, до тех пор, пока он не достигнет значения RowsNeeded
    SELECT r.InvoiceID, r.CompanyName, r.RowsNeeded, RowNumber + 1
    FROM RecursiveCTE r
    JOIN Table1 t ON r.InvoiceID = t.InvoiceID
    WHERE RowNumber < t.RowsNeeded
)

-- Часть для выбора итоговых данных
SELECT InvoiceID, CompanyName
FROM RecursiveCTE
ORDER BY InvoiceID, RowNumber
OPTION (MAXRECURSION 0);  -- Для предотвращения ограничения на рекурсию

Объяснение кода:

1. Инициализация CTE:

   • В первом запросе выбираются InvoiceID, CompanyName и RowsNeeded из таблицы Table1, при этом RowNumber инициализируется значением 1.

2. Рекурсивная часть:

   • Второй запрос использует рекурсию для добавления строк. Здесь мы соединяем рекурсивный CTE с основной таблицей по InvoiceID, и добавляем строки, увеличивая RowNumber до тех пор, пока он остается меньше, чем значение RowsNeeded.

3. Финальный выбор:

   • В последней части выбираются InvoiceID и CompanyName из полученного CTE, и вывод сортируется по InvoiceID и RowNumber.

4. Опция MAXRECURSION:

   • Установка OPTION (MAXRECURSION 0) позволяет выполнять рекурсию без ограничений, что важно, если ваши данные содержат высокие значения в RowsNeeded.

Результат:

Данный запрос возвратит строки в ожидаемом формате, где каждая запись будет повторена RowsNeeded раз, что обеспечит итоговое количество строк, равное 5 для предоставленных вами данных.

Теперь вы сможете использовать этот подход, чтобы динамически возвращать строки в зависимости от значений в вашей таблице.