mySql - 通过比较同一个表中的行进行更新

Question

我想通过将每一行与表中的所有其他行进行比较来更新列，但我不知道如何区分正在更新的行中的列名称和正在搜索的行。

这是一个简化的示例...

人:
+--------+-----+----------------+
|名称 |年龄 | nameClosestAge |
+--------+-----+----------------+
|爱丽丝 | 20 | |
|鲍勃 | 30| |
|克莱夫 | 22 | 22 |
|邓肯| 24 | |
+--------+-----+----------------+

要在“nameClosestAge”列中填写与每个人年龄最接近的人的姓名，您可以执行以下操作...

create temporary table peopleTemp like people;
insert into peopleTemp select * from people;
update people set nameClosestAge = 
(select name from peopleTemp where people.name != peopleTemp.name 
order by abs(people.age - peopleTemp.age) asc limit 1);

这会产生这个......
+--------+-----+----------------+
|名称 |年龄 | nameClosestAge |
+--------+-----+----------------+
|爱丽丝 | 20 |克莱夫 |
|鲍勃 | 30|邓肯 |
|克莱夫 | 22 | 22爱丽丝|
|邓肯| 25 | 25克莱夫 |
+--------+-----+----------------+

当然有一种方法可以在不创建重复表的情况下做到这一点。我正在寻找最有效的方法，因为我有一个非常大的表，并且更新时间太长。我将 mySql 与 PHP 结合使用。

Answer 1

您可以仅使用一个子查询而不使用临时表来执行此操作。

SELECT name, age, (
  SELECT name
  FROM people
  WHERE name != ppl.name
  ORDER BY ABS( people.age - ppl.age ) 
  LIMIT 1
  ) AS nameClosestAge
FROM people AS ppl;

已检查并有效:)

编辑:如果您希望能够使用计算后的行，您可以使用 View ；

CREATE VIEW people_close AS 
  SELECT name, age, (
    SELECT name
    FROM people
    WHERE name != ppl.name
    ORDER BY ABS( people.age - ppl.age ) 
    LIMIT 1
  ) AS nameClosestAge
  FROM people AS ppl;

您无法更新计算字段，但可以轻松查询。