选择值介于范围和值超出范围的最后一个默认行ID之间的行

Question

下面是一个示例表

MAX>>

ID	FROM	TO
100001	100.00	199.99	5
100002	200.00	299.99	4
100003	300.00	399.99	3
100004	400.00	499.99	2

使用TOP 1，首先根据您想要的标准进行订购，然后根据您的次要标准进行订购

DECLARE @VAL decimal(6,2) = 159.97;
SELECT TOP 1 ID, [FROM], [TO], [MAX]   
FROM [MyTable].[dbo].[MagicPlan]
ORDER BY
CASE WHEN @VAL BETWEEN [FROM] AND [TO] THEN 1 ELSE 0 END DESC
ID DESC;

就我个人而言，我会这样做，因为我觉得BETWEEN有点不直观。

SELECT TOP 1 ID, [FROM], [TO], [MAX]   
FROM [MyTable].[dbo].[MagicPlan]
ORDER BY
CASE WHEN [FROM] <= @VAL AND [TO] >= @VAL THEN 1 ELSE 0 END DESC
ID DESC;

DBFiddle.uk