在SQL中使用带Except的OFFSet子句

Question

我试图使用这个偏移量，因为我似乎不明白这个偏移量适合哪里。

如有任何帮助，将不胜感激

CREATE PROCEDURE [dbo].[get_bidLossModified]
@current_winner varchar (30), 
@limit varchar(30)
AS
BEGIN
SELECT 
vehicle_id, bid_value 
FROM 
auctionDetails 
WHERE 
current_winner = @current_winner 
OFFSET (@limit - 1)*10 ROWS
FETCH NEXT 10 ROWS ONLY
EXCEPT 
SELECT  
vehicle_id, MAX(bid_value)
FROM    
auctionDetails 
WHERE 
status = '0' 
GROUP BY 
vehicle_id
END

Answer 1

我怀疑您想要使用子查询：

SELECT ad.*
FROM ((SELECT vehicle_id, bid_value
FROM auctionDetails
WHERE current_winner=@current_winner 
) except
(SELECT  vehicle_id, MAX(bid_value)
FROM auctionDetails 
WHERE status = 0   -- probably a number so removed quotes
GROUP BY vehicle_id
)
) ad
ORDER BY vehicle_id, bid_value
OFFSET  (@limit - 1)*10 ROWS
FETCH NEXT 10 ROWS ONLY;