mysql - 使用另一个表中接近的值更新一个表

Question

我有两张 table 。

Products
------------

ID | Sales | Rank
==================
1  | 0     | 100
2  | 0     | 105
3  | 0     | 200
4  | 0     | 900

Sales

ID | Sales | Rank
==================
1  | 2000     | 99
2  | 5000     | 106
3  | 8000     | 800
4  | 2500     | 950

我想根据排名用 sales.sales 更新 sales.products 。例如

set products.sales=sales.sales where sales.sales is nearest to product.sales

在上述情况下，下面将是查询结果。

Products
------------

ID | Sales | Rank
==================
1  | 2000     | 100
2  | 5000     | 105
3  | 5000     | 200
4  | 2500     | 900

尝试根据排名从销售表中查找产品的销售额，如果未找到，则查找 Product.Rank 与 sales.rank 值最接近的任何内容。

谢谢

Answer 1

因为 MySQL 没有窗口函数，所以你必须以“困难的方式”来完成它。首先，构建一个查询来计算每个产品排名的最小差异:

select p.rank, min(abs(s.rank - p.rank)) diff
from sales s
cross join products p
group by 1

然后使用它来查找最接近的排名，方法是找到具有该差异的排名，再次加入并丢弃除最高的销售额之外的所有排名以打破平局:

update Products
join (select p.rank, min(abs(s.rank - p.rank)) diff
     from Sales s
     cross join Products p
     group by 1) x on Products.rank = x.rank
join Sales s1 on abs(Products.rank - s1.rank) = x.diff
left join Sales s2 on abs(Products.rank - s2.rank) = x.diff
    and s2.sales > s1.sales
    and s1.rank != s2.rank
set Products.sales = s1.sales
where s2.rank is null

<小时/>

由于 SQLFiddle 已停止运行，因此完整的脚本如下:

create table Products (ID int, Sales int, Rank int); 
insert into Products values 
(1,0,100),
(2,0,105),
(3,0,200),
(4,0,900);

create table Sales (ID int, Sales int, Rank int); 
insert into Sales values 
(1,2000,99),
(2,5000,106),
(3,8000,800),
(4,2500,950);

update Products
join (select p.rank, min(abs(s.rank - p.rank)) diff
     from Sales s
     cross join Products p
     group by 1) x on Products.rank = x.rank
join Sales s1 on abs(Products.rank - s1.rank) = x.diff
left join Sales s2 on abs(Products.rank - s2.rank) = x.diff
    and s2.sales > s1.sales
    and s1.rank != s2.rank
set Products.sales = s1.sales
where s2.rank is null;

select * from Products;

输出:

+------+-------+------+
| ID   | Sales | Rank |
+------+-------+------+
|    1 |  2000 |  100 |
|    2 |  5000 |  105 |
|    3 |  5000 |  200 |
|    4 |  2500 |  900 |
+------+-------+------+