mysql - 从两个表中搜索最接近的条目

Question

我有两个具有相同字段的表 A 和 B。我想做的是通过比较两个表中的不同字段来搜索表 B 中最接近的条目。 (或者基本上搜索表 B 中列出的 A 的基准)到目前为止我所做的是:

SELECT  A.Id , B.Id  FROM A , B
order by ABS (a.Volume - b.Volume)+ ABS(a.FCDate-b.FCDate)+ ABS(a.IssueDate-b.IssueDate)

此查询的问题在于它返回笛卡尔积。我想要的是对于表 A 中的每个条目只有一个来自表 B 的结果(例如前 1 个)。

举个例子:

Table A 
id        Volume        FCDate        IssueDate
1         100           2014-01-01    2012-01-01

Table B
id        Volume        FCDate        IssueDate
1         95            2012-07-15    2000-01-01
2         100           2014-02-15    2004-01-01
3         100           2014-02-15    2011-09-20

查询的结果必须是:

A.Id       B.Id
1          3

在这种情况下，使用上面的线性公式可以立即得到结果。但是根据我拥有的数据(+4000 个条目)，我有时会得到 3 或 4 个结果。在这种情况下，我认为选择前 1 名就可以了

Answer 1

认为您需要一个子查询来获取 A 上每条记录的最小差异，然后连接到差异相同的 B。

SELECT  A.Id AS aid, B.Id AS bid
FROM A 
INNER JOIN
(
    SELECT  A.Id , MIN(ABS (a.Volume - b.Volume)+ ABS(a.FCDate-b.FCDate)+ ABS(a.IssueDate-b.IssueDate))  AS MinDiff
    FROM A 
    CROSS JOIN B
    GROUP BY A.Id
) sub0
ON A.Id = sub0.Id
INNER JOIN B
ON ABS (a.Volume - b.Volume)+ ABS(a.FCDate-b.FCDate)+ ABS(a.IssueDate-b.IssueDate) = sub0.MinDiff

考虑到 JOIN 所需的计算，我不希望这很快。如果 B 上的 2 条记录与 A 上的一条记录具有相同的差异，它也会遇到困难

SQL fiddle 在这里:-

http://www.sqlfiddle.com/#!2/03987/3