MySQL：用新表中最接近的值替换列的值

发布于 2024-10-16 03:23:37 字数 503 浏览 2 评论 0原文

我有两个表，如下所示：

Table1（ID、公里、深度）
Table2（ID、公里、深度）

Sample Data:
Table 1
1, 0.001, 10
2, 0.002, 11
3, 0.003, 11

Table 2
1, 0.001, 10
2, 0.003, 12
3, 0.004, 15

我需要根据公里值将表 1 中的深度替换为表 2 中的深度。

但是，对于表 1 中的每个人，表 2 中可能没有公里值。因此，我需要获取最接近的值（以公里为单位）并在替换中使用其深度。

我希望用一条 SQL 语句来实现这一点。只是直接替换就像：

UPDATE T1, T2 SET T1.Depth = T2.Depth WHERE T1.Kilometers = T2.Kilometers

我可以通过什么方法来调整它以获得最接近的值？

原文

I have two tables like so:

Table1 (ID, Kilometers, Depth)
Table2 (ID, Kilometers, Depth)

Sample Data:
Table 1
1, 0.001, 10
2, 0.002, 11
3, 0.003, 11

Table 2
1, 0.001, 10
2, 0.003, 12
3, 0.004, 15

I need to replace the depth in table 1 with the depth in table 2 according to its Kilometers value.

However, there may not be a kilometers value in table2 for everyone in table 1. So i need to get the closest value (by kilometer) and use its depth in the replace.

I was hoping for a single SQL statement to acheive this.
Just a straight replace would be like:

UPDATE T1, T2 SET T1.Depth = T2.Depth WHERE T1.Kilometers = T2.Kilometers

Any way i can adapt this to get the closest value?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅蓝的眸勾画不出的柔情 2024-10-23 03:23:37

这很简单并且可以满足您的要求：

UPDATE table1 SET depth = (
     SELECT depth FROM table2 
     ORDER BY (ABS(table1.kilometers-table2.kilometers)) ASC 
     LIMIT 1
);

-- Query OK, 2 rows affected (0.00 sec)

mysql> select * from table1;
+----+------------+-------+
| id | kilometers | depth |
+----+------------+-------+
|  1 |      0.001 |    10 |
|  2 |      0.002 |    10 |
|  3 |      0.003 |    12 |
+----+------------+-------+

This is simple and does what you want:

UPDATE table1 SET depth = (
     SELECT depth FROM table2 
     ORDER BY (ABS(table1.kilometers-table2.kilometers)) ASC 
     LIMIT 1
);

-- Query OK, 2 rows affected (0.00 sec)

mysql> select * from table1;
+----+------------+-------+
| id | kilometers | depth |
+----+------------+-------+
|  1 |      0.001 |    10 |
|  2 |      0.002 |    10 |
|  3 |      0.003 |    12 |
+----+------------+-------+

回复收藏 0 原文

丘比特射中我 2024-10-23 03:23:37

update tbl1
inner join (
    select tbl1.id,
        (
            select id
            from tbl2
            order by abs(tbl2.Kilometers - tbl1.Kilometers) asc
            limit 1
        ) AS tbl2id
    from tbl1
) X
    on tbl1.id = X.id
inner join tbl2
    on tbl2.id = X.tbl2id

set tbl1.depth = tbl2.depth;

注意：tbl1 和 tbl2 是两个表名称。 X 是为子查询指定的别名，用于确定每个 tbl1.id 最接近的匹配项。 tbl2id 是 tbl2 中最接近每个 tbl1.id 的 id，在子查询中使用别名。

update tbl1
inner join (
    select tbl1.id,
        (
            select id
            from tbl2
            order by abs(tbl2.Kilometers - tbl1.Kilometers) asc
            limit 1
        ) AS tbl2id
    from tbl1
) X
    on tbl1.id = X.id
inner join tbl2
    on tbl2.id = X.tbl2id

set tbl1.depth = tbl2.depth;

Note: tbl1 and tbl2 are the two table names. X is an alias given to the subquery that determines the closest match per tbl1.id. tbl2id is the id from tbl2 that is closest to each tbl1.id, aliased in the subquery.

回复收藏 0 原文

~没有更多了~