Заменить курсоры запросами

Question

Допустим, у меня есть бронирование на 6 часов и 3 скидки на 2 часа каждый. Я хочу разделить свое бронирование на 3 части, чтобы я мог выделить 2 часа на каждую скидку.

Это вернуло бы что-то вроде этого:

BookingId 1 | DiscountId 1 | Qty 2
BookingId 1 | DiscountId 2 | Qty 2
BookingId 1 | DiscountId 3 | Qty 2

Затем я вставил бы эти записи в другую таблицу.

Я использую сильно оптимизированный запрос, чтобы определить количество часов, доступных для каждой скидки. Однако я не могу найти "хороший" способ распределить свое бронирование по каждой скидке без использования курсора.

(...)

WHILE @@FETCH_STATUS = 0 
BEGIN
    IF @RequiredQty = 0 
       RETURN

    IF @RequiredQty <= @AvailableQty  
    BEGIN
        INSERT INTO discount.Usage (DiscountId, BookingId, Quantity) 
        VALUES (@DiscountId, @BookingId, @RequiredQty)

        SET @RequiredQty = 0
    END

    IF @RequiredQty > @AvailableQty  
    BEGIN
        INSERT INTO discount.Usage (DiscountId, BookingId, Quantity) 
        VALUES (@DiscountId, @BookingId, @AvailableQty)

        SET @RequiredQty -= @AvailableQty
    END

    FETCH NEXT FROM ecursor INTO @DiscountId, @AvailableQty
END

DEALLOCATE ecursor

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

Спасибо, Себ

Answer 1

Ты можешь использоватьCTE RECURSIVE составить таблицу.

как это.

DECLARE @BookingId INT = 1;
DECLARE @RequiredQty INT = 2;
DECLARE @Hours INT = 7;

CREATE TABLE #T
(
    BookingId  INT,
    DiscountId INT,
    Quantity   INT
)

;WITH CTE([Count],[Quantity],Rk) AS  
(
    SELECT
           CASE 
                WHEN [HOURS] - @RequiredQty > @RequiredQty THEN @RequiredQty
                ELSE [HOURS] - @RequiredQty
           END ,
           T.HOURS,1
    FROM
    (
        SELECT @Hours [HOURS]
    ) AS T
    UNION ALL
    SELECT CASE  
                WHEN CTE.[Quantity] - @RequiredQty > @RequiredQty THEN @RequiredQty
                ELSE CTE.[Quantity] - @RequiredQty
           END AS [Count],
           CTE.[Quantity] - @RequiredQty, 
           RK + 1
    FROM CTE
    WHERE CTE.[Quantity] - @RequiredQty > 0
)

INSERT INTO #T(BookingId,DiscountId,Quantity)
SELECT @BookingId,Rk,[Count] FROM CTE
option (maxrecursion 0)

select * from #T

SQLDEMO

Answer 2

Это другой подход, но не знаю, имеет ли этот код лучшую производительность, чем курсор.

DECLARE @DiscountStocks TABLE (Id INT IDENTITY(1,1), DiscountId INT, LastQty INT)
INSERT INTO @DiscountStocks (DiscountId, LastQty) VALUES (1, 5)
INSERT INTO @DiscountStocks (DiscountId, LastQty) VALUES (2, 2)
INSERT INTO @DiscountStocks (DiscountId, LastQty) VALUES (3, 1)

DECLARE @DiscountBookings TABLE (Id INT IDENTITY(1,1), DiscountId INT, BookingId INT, Qty INT)

DECLARE @BookingDiscount TABLE (Id INT IDENTITY(1,1), BookingId INT, DiscountId INT, Qty INT)
INSERT INTO @BookingDiscount (BookingId, DiscountId, Qty) VALUES (1, 1, 4)
INSERT INTO @BookingDiscount (BookingId, DiscountId, Qty) VALUES (1, 2, 2)
INSERT INTO @BookingDiscount (BookingId, DiscountId, Qty) VALUES (1, 3, 1)
INSERT INTO @BookingDiscount (BookingId, DiscountId, Qty) VALUES (2, 1, 1)
INSERT INTO @BookingDiscount (BookingId, DiscountId, Qty) VALUES (2, 2, 2)

SELECT BD.Id AS BDId, DS.Id AS DSId, DS.LastQty, BD.Qty
, DS.LastQty - (SELECT SUM(Qty) FROM @BookingDiscount WHERE Id <= BD.Id AND DiscountId = BD.DiscountId) AS QtyAfterSubstract
INTO #LastDiscountStock
FROM @DiscountStocks DS
INNER JOIN @BookingDiscount BD ON DS.DiscountId = BD.DiscountId
ORDER BY BD.Id, DS.Id

INSERT INTO @DiscountBookings (DiscountId, BookingId, Qty)
SELECT DSId, BDId, Qty
FROM #LastDiscountStock
WHERE QtyAfterSubstract >= 0

DROP TABLE #LastDiscountStock

SELECT * FROM @DiscountBookings