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,但是如果我需要创建一个存储过程来创建一个临时表来存储这些值,那就可以了。
链接到SQL小提琴的模式
It's late and I think my brain shut off, so any help is appreciated.
已经很晚了,我想我的大脑已经关闭了,所以我很感激你的帮助。
Thanks.
谢谢。
3 个解决方案
#1
5
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
#2
0
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 null
s, 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解决方案
#3
0
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
#1
5
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
#2
0
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 null
s, 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解决方案
#3
0
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