SQL 获取最接近数字的值

Question

我需要从"数量"列中除以中找到每个数字的最接近值，然后将两个数量的"值"列中的值放在"值"列中。

例： 在"除以"列中，值 5166 最接近"数量"列值 5000。为了防止多次使用这两个值，我需要将值 5000 放在两个数字的值列中，如下例所示。另外，是否可以在没有循环的情况下执行此操作？

Quantity    Divide  Rank         Value
15500       5166    5            5000
1250        416     5            0
5000        1666    5            5000
12500       4166    4            0
164250      54750   3            0
5250        1750    3            0
6250        2083    3            0
12250       4083    3            0
1750        583     2            0
17000       5666    2            0
2500        833     2            0
11500       3833    2            0
1250        416     1            0

Answer 1

这里有几个答案，但它们都使用 ctes/复杂子查询。有一种更简单/更快的方法，只需进行几次自我加入和分组

https://www.db-fiddle.com/f/rM268EYMWuK7yQT3gwSbGE/0

select 
min(min.quantity) as minQuantityOverDivide
, t1.divide
, max(max.quantity) as maxQuantityUnderDivide
, case 
when 
(abs(t1.divide - coalesce(min(min.quantity),0)) 
<
abs(t1.divide - coalesce(max(max.quantity),0)))
then max(max.quantity)
else min(min.quantity) end as cloestQuantity
from t1
left join (select quantity from t1) min on min.quantity >= t1.divide
left join (select quantity from t1) max on max.quantity < t1.divide
group by    
t1.divide

Answer 2

如果我理解要求，5166并不接近5000- 它接近5250(166与84的增量(

相应的查询，没有循环，应该是(在这里摆弄：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=be434e67ba73addba119894a98657f17(。

(我添加了一个Value_Rank，因为它不确定您是否要保留或重新计算Rank(

select
Quantity, Divide, Rank, Value,
dense_rank() over(order by Value) as Value_Rank
from
(
select
Quantity, Divide, Rank,
--
case
when abs(Quantity_let_delta) < abs(Quantity_get_delta) then Divide + Quantity_let_delta
 else Divide + Quantity_get_delta
end as Value
from
(
select
so.Quantity, so.Divide, so.Rank,
-- There is no LessEqualThan, assume GreaterEqualThan
max(isnull(so_let.Quantity, so_get.Quantity)) - so.Divide as Quantity_let_delta,
-- There is no GreaterEqualThan, assume LessEqualThan
min(isnull(so_get.Quantity, so_let.Quantity)) - so.Divide as Quantity_get_delta
from
SO so
left outer join SO so_let
on so_let.Quantity <= so.Divide
--
left outer join SO so_get
on so_get.Quantity >= so.Divide
group by so.Quantity, so.Divide, so.Rank
) so
) result

或者，如果最接近的意思是前一个最接近的(在这里小提琴：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b41fb1a3fc11039c7f82926f8816e270(。

select
Quantity, Divide, Rank, Value,
dense_rank() over(order by Value) as Value_Rank
from
(
select
so.Quantity, so.Divide, so.Rank,
-- There is no LessEqualThan, assume 0
max(isnull(so_let.Quantity, 0)) as Value
from
SO so
left outer join SO so_let
on so_let.Quantity <= so.Divide
group by so.Quantity, so.Divide, so.Rank
) result

Answer 3

你不需要循环，基本上你需要找到除法和所有数量(第一个 cte(之间的最小差异。然后使用此距离查找相应的记录(第二个 cte(，然后与初始表联接以获取转换后的值(最终选择(

;with cte as (
select t.Divide, min(abs(t2.Quantity-t.Divide)) as ClosestQuantity
from #t1 as t
cross apply #t1 as t2
group by t.Divide
)
,cte2 as (
select distinct
t.Divide, t2.Quantity
from #t1 as t
cross apply #t1 as t2
where abs(t2.Quantity-t.Divide) = (select ClosestQuantity from cte as c where c.Divide = t.Divide)
)
select t.Quantity, cte2.Quantity as Divide, t.Rank, t.Value
from #t1 as t
left outer join cte2 on t.Divide = cte2.Divide