从表1中查找值，其中表2中的值位于表1中的行之间

Assume the following structure:

假设以下结构:

Items:

项目:

ItemId  Price
----------------
1000    129.95
2000    49.95
3000    159.95
4000    12.95

Thresholds:

阈值:

PriceThreshold  Reserve
------------------------
19.95           10
100             5
150             1

-- PriceThreshold is the minimum price for that threshold/level

I'm using SQL Server 2008 to return the 'Reserve' based on where the item price falls between in 'PriceThreshold'.

我使用SQL Server 2008返回“Reserve”，基于“PriceThreshold”中项目价格在何处下降。

Example:

例子:

ItemId  Reserve
1000    5
2000    10
3000    1

--Price for ItemId 4000 isn't greater than the lowest price threshold so should be excluded from the results.

——ItemId 4000的价格不超过最低价格门槛，不计入结果。

Ideally I'd like to just be able to use some straight T-SQL, but if I need to create a stored procedure to create a temp table to store the values that would be fine.

理想情况下，我希望能够使用一些直接的T-SQL，但是如果我需要创建一个存储过程来创建一个临时表来存储这些值，那就可以了。

Link to SQL Fiddle for schema

链接到SQL小提琴的模式

It's late and I think my brain shut off, so any help is appreciated.

已经很晚了，我想我的大脑已经关闭了，所以我很感激你的帮助。

Thanks.

谢谢。

3 个解决方案

#1

Interested in something like this:

对这样的东西感兴趣:

select
  ItemId, 
  (select top 1 Reserve
   from Threshold 
   where Threshold.PriceThreshold < Items.Price
    order by PriceThreshold desc) as Reserve
from
  Items
where
  Price > (select min(PriceThreshold) from Threshold)

SQLFiddle

SQLFiddle

#2

One way to go about this is to use reserve as the lower boundary of the range, and use the lead analytic function to generate the "next" lower boundry, i.e., the top boundary.

一种方法是用储备作为范围的下边界，用领先解析函数生成“下一个”下边界，即。,顶部边界。

Once you've done this, it's simply a matter of joining with the condition that the price should be between both boundaries. Unfortunately, the between operator doesn't handle nulls, so you'll need to use a somewhat clunky condition to handle the first and last rows:

一旦你这样做了，这仅仅是加入价格应该在两个边界之间的条件。不幸的是，中间操作符不能处理null，所以您需要使用一个有点笨拙的条件来处理第一行和最后一行:

SELECT [ItemId], [Reserve]
FROM   Items
JOIN   (SELECT [PriceThreshold] AS [Bottom], 
               LEAD([PriceThreshold]) OVER (ORDER BY [PriceThreshold]) AS [Top],
               [Reserve] 
        FROM   [Threshold]) t ON 
               [Price] Between [Bottom] AND [Top] OR
               ([Top] IS NULL AND [Price] > [Bottom]) OR
               ([Bottom] IS NULL AND [Price] < [Top])

SQLFiddle solution

SQLFiddle解决方案

#3

You can get the lower bound with

你可以得到下界

select i.itemid, max(lo.pricethreshold) as lo
from items i
join threshold lo on lo.pricethreshold <= i.price
group by i.itemid

and use this to retrieve the reserve

然后用这个来取回储备

with bounds as (select i.itemid, max(lo.pricethreshold) as lo
                from items i
                join threshold lo on lo.pricethreshold <= i.price
                group by i.itemid)
select i.itemid, t.reserve
from items i
join bounds b on b.itemid = i.itemid
join threshold t on t.pricethreshold = b.lo

SQLFiddle

SQLFiddle