mysql - 如何加入特定的表行？

Question

table_one

+------+-------------+
| id   | cool        |
+------+-------------+
| 1    | 1.58        |
| 2    | 8.88        |
+------+-------------+

table_two

+------+-------------+
| id   | okies       |
+------+-------------+
| 1    | 2.15        |
| 2    | 7.50        |
+------+-------------+

table_result

+------+-------------------+
| id   | result            |
+------+-------------------+
| 1    | 1.58 (min value)  |
| 2    | 7.50 (min value)  |
+------+-------------------+

你好，我对mysql比较熟悉。我正在使用 least() 函数来查找 table_one 和 table_two 之间的最小值:

INSERT INTO table_result (id, result)
select table_one.id, least(table_one.cool, table_two.okies) val
from table_one 
  join table_two on table_one.id = table_two.id

ON DUPLICATE KEY UPDATE id=VALUES(id), result=VALUES(result)

此代码运行完美，除了我不想(评估)获取所有表行(id 1 和 id 2)的最小值。我想评估/定位特定的行 ID(例如:仅限 id 2!)。

我尝试添加 WHERE id = 2 但我一直收到错误。

我怎样才能做到这一点？

Answer 1

What errors are you getting? Perhaps you just need to alias the id column in your where critera... – sgeddes

我遇到的错误是Column 'id' in where clause is ambiguous。研究这个错误后发现，我需要指定哪个表 ID，因为两个表都有 ID。

INSERT INTO table_result (id, result)
select table_one.id, least(table_one.cool, table_two.okies) val
from table_one 
  join table_two on table_one.id = table_two.id
  WHERE table_one.id = 2
ON DUPLICATE KEY UPDATE id=VALUES(id), result=VALUES(result)