使用其他表中最接近的表示形式添加具有预计算值的列

Question

假设我有一个下表

id  value1  value2  value1_plus_value2
0   1.0     2.2     null
1   2.1     3.5     null
2   5.2     2.9     null
3   1.5     1.4     null

和用于映射值的表

value   name
2.0     "almost three"
3.0     "about three"
3.5     "three and half"
5.5     "five point five"
6.0     "a lot more than five"

生成的表应如下所示

id  value1  value2  value1_plus_value2
0   1.0     2.2     "about three"
1   2.1     3.5     "five point five"
2   5.2     2.9     "a lot more than five"
3   1.5     1.4     "about three"

应该如何做到这一点？使用一些外部脚本和使用循环逐行执行相当简单，但很可能也有一些 sql 方法可以做到这一点;

通过以下操作，我可以为一行执行此操作：

select id, value1, value2, name, abs(value1+value2-value) as distance
from value, mapping
where id=1
group by distance
limit 1;

添加了 sqlfiddle 链接 http://sqlfiddle.com/#!2/b1ac73/1

我想对所有行执行此操作。并将找到的最接近的名称添加到值表中。

所以我想得到的表的元代码是这样的

select outerquery.value1, outerquery.value2, name as value1_plus_value2
from value as outerquery,
(
select id, value1, value2, name, abs(value1+value2-value) as distance
from value, mapping
where id = outerquery.id
group by distance
limit 1
) as subquery;

但显然我无法将数据从外部查询传递到子查询。但我敢肯定，我只是想错了。

Answer 1

修改以适应精细的标准...

SELECT value.*,mapping.* 
  FROM value
  JOIN mapping
  JOIN
     ( SELECT value.id
            , MIN(ABS((value.value1+value.value2)-mapping.value))min_abs 
         FROM value
         JOIN mapping
        GROUP 
           BY id
     ) x
    ON x.id = value.id
   AND x.min_abs = ABS((value.value1+value.value2)-mapping.value)
 ORDER
    BY id;

http://sqlfiddle.com/#!2/b1ac73/6