顶部、限制、偏移...我无法在 SQL Server 中显示寄存器

Question

我有一个光标来循环浏览两个过滤的记录，它做得很好。但是在每条记录中，我需要显示 5 个最差的库存值。它只向我显示一条，我已经尝试了 TOP(LIMIT 在 SQL Server 中不起作用(，现在还有 OFFSET 和 FETCH NEXT，但它仍然只显示第一条记录。

有人看到任何合乎逻辑的解决方案吗？ 我以为这很容易，但我找不到原因。

DECLARE @Counter AS INT = 1;
DECLARE @LocationBuffer  AS NVARCHAR(30);
DECLARE @QuantityBuffer  AS INT;
DECLARE @MinDateMovements  AS DATE;
DECLARE @TopDateMovements  AS DATE;

DECLARE @DateMovementsTopWorst AS DATE;
DECLARE @InventoryOnSiteTopWorst AS NUMERIC;
DECLARE @FamilyTopWorst AS VARCHAR(50)
DECLARE @BufferTopWorst AS NUMERIC;
DECLARE @SkuTopWorst AS VARCHAR(50);
DECLARE @LocationTopWorst AS VARCHAR(50); 
DECLARE cursorr CURSOR LOCAL
FOR SELECT amor.LOCATION, COUNT(amor.INACTIVE),
MIN(UPDATE_MOVEMENTS), MAX(UPDATE_MOVEMENTS)
FROM TFSCM_CLI_BUFFER AS amor
WHERE amor.INACTIVE = 0 AND amor.FAMILY IN
(SELECT fam.CDBUFFER_FAMILY 
FROM TFSCM_CLI_BUFFER_FAMILY AS fam
WHERE fam.REPLENISHMENT_MODE = 'M' OR fam.REPLENISHMENT_MODE = 'I')
GROUP BY amor.LOCATION;
OPEN cursorr
FETCH NEXT FROM cursorr
INTO @LocationBuffer, @QuantityBuffer, @MinDateMovements, @TopDateMovements;
WHILE (@@FETCH_STATUS = 0) 
BEGIN
PRINT 'Locations: '
+ CONVERT(VARCHAR(100), @LocationBuffer );
PRINT 'Buffers (manual and intermediate) actives: '
+ CONVERT(VARCHAR(100), @QuantityBuffer );
PRINT 'Movements dates: '
+ CONVERT(VARCHAR(100), @MinDateMovements )
+ ' and '
+ CONVERT(VARCHAR(100), @TopDateMovements);
PRINT '------------------------------------------------------------------------------------------------------------'    
SELECT  @SkuTopWorst= SKU,
@BufferTopWorst = BUFFER,
@FamilyTopWorst = FAMILY,
@InventoryOnSiteTopWorst = TOTAL_INVENTORY_ON_SITE,
@DateMovementsTopWorst = UPDATE_MOVEMENTS,
@LocationTopWorst = LOCATION
FROM TFSCM_CLI_BUFFER
WHERE LOCATION = @LocationBuffer
ORDER BY TOTAL_INVENTORY_ON_SITE
OFFSET  5 ROWS 
FETCH NEXT 5 ROWS ONLY 

PRINT ' SKU: '
+ '         Size: '
+ '         Family: '
+ '         Inventory: '
+ '         Movement Date: '
PRINT   @SkuTopWorst
+ '     '
+ CONVERT(VARCHAR(100), @BufferTopWorst)
+ '             '
+  @FamilyTopWorst
+ '                 '
+ CONVERT(VARCHAR(100), @InventoryOnSiteTopWorst)
+ '                 '
+  CONVERT(VARCHAR(100), @DateMovementsTopWorst);
PRINT '------------------------------------------------------------------------------------------------------------'
FETCH NEXT FROM cursorr INTO @LocationBuffer, @QuantityBuffer, @MinDateMovements, @TopDateMovements
END
CLOSE cursorr
DEALLOCATE cursorr

Answer 1

当您select @var1 = value1, @var2 = value2, ...时，每个变量将填充正好 1 个值。

如果要打印 5 个值，则需要将光标游标在 5 条记录中，并将每组值提取到变量中，然后打印它们。

或者您可以使用字符串连接将整个列表构建为带有 cr/lf 的字符串并打印 clob：

set @Info = (
SELECT top 5
cast(SKU    as char(15))
+ cast(BUFFER as char(15))
+ cast(FAMILY as char(17))
-- rest of the columns here
+ char(13)+char(10)
FROM TFSCM_CLI_BUFFER
WHERE LOCATION = @LocationBuffer
ORDER BY TOTAL_INVENTORY_ON_SITE
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)');
print @Info;

它使用for xml path来连接字符串。

如果你使用的是 SQL Server 2017+，则可以改用string_agg()