SQL查询以最接近的值联接两个表 [英] SQL Query To Join Two Tables On Closest Value

问题描述

>我具有下面的数据，如下面的前两个图像所示.

">"I have the following data as shown below in the first two images.

dbo.Value1:     ID       Value       Date
                349383   -3268.32    7/14/2015
                573832   -1732.24    7/14/2015
                747583    49.85      7/14/2015
                958383    231.36     7/14/2015
                172737    1734.94    7/14/2015
                593983    25908.29   7/14/2015

dbo.ProductValue: ProductID  ProductValue   UploadDate
                  NULL       -6348          7/14/2015
                  958383      232           7/14/2015
                  172737      1735          7/14/2015
                  NULL        15392         7/14/2015

我想基于值"和产品值"列完全连接两个表，以便两个表基于最接近的值连接，如下所示.有人知道我将如何做到这一点吗?

I want to fully join the two tables based on the Value and Product Value columns, so that the two tables join based on the closest values, as shown below. Does anybody know how I would be able to do this?

enter code here: ID      Value      Date        ProductID   ProductValue    UploadDate
                 349383 -3268.32    7/14/2015   NULL        -6348           7/14/2015
                 573832 -1732.24    7/14/2015   NULL         NULL           NULL
                 747583  49.85      7/14/2015   NULL         NULL           NULL
                 958383  231.36     7/14/2015   958383       232            7/14/2015
                 172737  1734.94    7/14/2015   172737       1735           7/14/2015
                 593983  25908.29   7/14/2015   NULL         15392          7/14/2015

推荐答案

不确定要查找的内容，但这将匹配第二个表id与productid的行，如果productid为null，则为对于所有行都匹配，并且值与乘积值之间的差异最小的行就是将被分配的行.

Not exactly sure what you're looking for, but this will match the rows from the second table id vs productid and if productid is null, then it will be matched for all rows, and the row that has the lowest difference between value vs. product value is the one where it will be assigned.

select *
from
  value v
  outer apply (
    select top 1 *, abs(p.productvalue - v.value) as diff
    from productvalue p
    where (p.productid = v.id or p.productid is null)
    and not exists (select 1 from value v2 where 
        (p.productid = v2.id or p.productid is null) and
        abs(p.productvalue - v.value) > abs(p.productvalue - v2.value))
    order by case when p.productid is null then 2 else 1 end,
      abs(p.productvalue - v.value) asc
  ) p

按order by排序的外部apply将选择最匹配的行，内部的not exists子句将仅将其与具有最接近乘积值的行匹配.

The outer apply with order by will select the best matching row, and the not exists clause inside it will only match it to the row that has closest product value.

您可以在 SQL小提琴

这篇关于SQL查询以最接近的值联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！