我正在嘗試創建一個查詢，該查詢將從一個表中找到最接近的值並將其 ID 返回到結果表中。

下面是一個應該更好地描述這種情況的例子。

樣本數據

這兩個表將存在於 SQL 數據庫中。

主表

+----+-------------+
| ID | Measurement |
+----+-------------+
|  1 | 0.24        |
|  2 | 0.5         |
|  3 | 0.14        |
|  4 | 0.68        |
+----+-------------+

查找表

+----+---------------+
| ID | Nominal Value |
+----+---------------+
|  1 | 0.1           |
|  2 | 0.2           |
|  3 | 0.3           |
|  4 | 0.4           |
|  5 | 0.5           |
|  6 | 0.6           |
|  7 | 0.7           |
|  8 | 0.8           |
|  9 | 0.9           |
+----+---------------+

目標

這將是查詢的結果。測量值不應在邊界上（例如 0.25）。

+----+-------------+-----------+
| ID | Measurement | Lookup ID |
+----+-------------+-----------+
|  1 | 0.24        |         2 |
|  2 | 0.5         |         5 |
|  3 | 0.14        |         1 |
|  4 | 0.68        |         7 |
+----+-------------+-----------+

是否有能夠返回這種結果的查詢？

幾個查詢經過測試和優化。所有返回都一樣，基本上都是標準的SQL。（但沒有 RDBMS 完全支持該標準。）

第一個使用12c 或 MySQL 之前的 Oracle 中缺少的LATERAL JOINa 。測試自己哪個表現最好。

lookup它們都對Postgres 中的表使用僅索引掃描。顯然，lookup.nominal_value需要被索引。我建議這樣做，UNIQUE因為該列似乎應該是唯一的。這會自動創建最重要的索引。

LATERAL加入

SELECT m.id, m.measurement, l.nominal_value
FROM   measurement m
JOIN   LATERAL (
  (
  SELECT nominal_value - m.measurement AS diff, nominal_value
  FROM   lookup
  WHERE  nominal_value >= m.measurement
  ORDER  BY nominal_value
  LIMIT  1
  )
  UNION  ALL
  (
  SELECT m.measurement - nominal_value, nominal_value
  FROM   lookup
  WHERE  nominal_value <= m.measurement
  ORDER  by nominal_value DESC
  LIMIT  1
  )
  ORDER  BY 1
  LIMIT  1
  ) l ON true;

所需的所有括號UNION。看：

• Postgres 9.2 在一個查詢中選擇多個特定行

子查詢中的相關子查詢

SELECT id, measurement
    , CASE WHEN hi - measurement > measurement - lo
           THEN lo
           ELSE hi
      END AS nominal_value
FROM  (
  SELECT id, measurement
       , ( SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value >= m.measurement
           ORDER  BY nominal_value
           LIMIT  1) AS hi
        , COALESCE((
           SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value <= m.measurement
           ORDER  by nominal_value DESC
           LIMIT  1), 0) AS lo   -- cover possible NULL values
  FROM   measurement m
  ) sub;

CTE 中的相關子查詢

WITH cte AS (
  SELECT id, measurement
       , ( SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value >= m.measurement
           ORDER  BY nominal_value
           LIMIT  1) AS hi
       , COALESCE((
           SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value <= m.measurement
           ORDER  by nominal_value DESC
           LIMIT  1), 0) AS lo   -- cover possible NULL values
  FROM   measurement m
  )
SELECT id, measurement
    , CASE WHEN hi - measurement > measurement - lo
           THEN lo
           ELSE hi
      END AS nominal_value
FROM   cte;

嵌套相關子查詢

SELECT id, measurement
    , (SELECT nominal_value FROM (
        (
        SELECT nominal_value - m.measurement, nominal_value
        FROM   lookup
        WHERE  nominal_value >= m.measurement
        ORDER  BY nominal_value
        LIMIT  1
        )
        UNION  ALL
        (
        SELECT m.measurement - nominal_value, nominal_value
        FROM   lookup
        WHERE  nominal_value <= m.measurement
        ORDER  by nominal_value DESC
        LIMIT  1
        )
        ORDER  BY 1
        LIMIT  1
        ) sub
        ) AS nominal_value
FROM   measurement m;

db<>fiddle here

舊sqlfiddle

不確定您使用的是什麼 DBMS，但現在有不少支持視窗函式：

SELECT id, measurement, lookupid
FROM (
   SELECT t1.id, t1.measurement, t2.id as lookupid
        , row_number() over (partition by t1.id
                             order by abs(t1.measurement - t2.nominal) desc
                            ) as rn
   FROM main t1
   CROSS JOIN lookup t2
) AS T
WHERE rn = 1;

引用自：https://dba.stackexchange.com/questions/73804