为什么我无法在 SQL Server 存储过程中使用 LIMIT 和 OFFSET

Question

我正试图使用以下查询获取MAX-1记录，但如果我在查询中使用LIMIT和OFFSET，则会引发错误。有其他方法可以使用此查询来获取最大-1行吗？我正在使用SQL Server

UPDATE [dbo].[ORDER_DETAIL] 
SET LINE_STATUS_CODE = 'C' 
WHERE RATE_STRUCTURE = 'LIST' 
  AND RATE_CODE = 'PRIMARY' 
  AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id  
  AND [ORDER_NO] = @ip_order_id 
  AND ORDER_LINE_NO = (SELECT ORDER_LINE_NO 
                       FROM [dbo].[ORDER_DETAIL] 
                       WHERE RATE_STRUCTURE = 'LIST' 
                         AND ORDER_NO = @ip_order_id 
                         AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id 
                       LIMIT 1 OFFSET 1 
                       ORDER BY ORDER_LINE_NO DESC)

Answer 1

这是您应该用于内部选择的确切语法：

SELECT ORDER_LINE_NO 
FROM [dbo].[ORDER_DETAIL] 
WHERE RATE_STRUCTURE = 'LIST' 
AND ORDER_NO = @ip_order_id 
AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id 
ORDER BY ORDER_LINE_NO DESC
OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY

然后你的更新声明会是这样的：

UPDATE [dbo].[ORDER_DETAIL] SET LINE_STATUS_CODE ='C' 
WHERE RATE_STRUCTURE = 'LIST' 
AND RATE_CODE= 'PRIMARY' 
AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id
AND [ORDER_NO] = @ip_order_id  
AND ORDER_LINE_NO = (SELECT ORDER_LINE_NO 
                     FROM [dbo].[ORDER_DETAIL] 
                     WHERE RATE_STRUCTURE = 'LIST' 
                     AND ORDER_NO = @ip_order_id  
                     AND SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id
                     ORDER BY ORDER_LINE_NO DESC
                     OFFSET 1 ROW
                     FETCH NEXT 1 ROW ONLY);

这是一个小型演示

附言：SQL Server没有LIMIT，您可以将TOP或OFFSET与FETCH NEXT一起使用。如果要使用TOP，则不能使用偏移从第二个结果或第三个结果开始。

Answer 2

如果我理解正确，根据您设置的匹配和筛选标准，您想要类似于Order_Detail倒数第二条记录的内容：

UPDATE od
    SET LINE_STATUS_CODE ='C'
    FROM (SELECT od.*, ROW_NUMBER() OVER (ORDER BY ORDER_LINE_NO DESC) as seqnum
          FROM [dbo].[ORDER_DETAIL] od
          WHERE RATE_STRUCTURE = 'LIST' AND RATE_CODE= 'PRIMARY' AND
                SHIP_MASTER_CUSTOMER_ID = @ip_master_customer_id  AND 
                [ORDER_NO] = @ip_order_id 
        ) od
    WHERE seqnum = 2;