56,687
社区成员
发帖
与我相关
我的任务
分享
mysql> select * from map_resource_node;
+------+------+------+---------+
| id | x | y | city_id |
+------+------+------+---------+
| 1 | 11 | 10 | NULL |
| 2 | 30 | 25 | NULL |
| 3 | 40 | 34 | NULL |
+------+------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from city;
+------+------+------+
| id | x | y |
+------+------+------+
| 1 | 20 | 14 |
| 2 | 30 | 10 |
| 3 | 13 | 45 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> update map_resource_node map_node inner join(
-> select c.id cid,map.id mid from city c,map_resource_node map
-> where not exists(
-> select 1 from city ct,map_resource_node m
-> where m.id=map.id and
-> power(ct.x-m.x,2)+power(ct.y-m.y,2)<
-> power(c.x-map.x,2)+power(c.y-map.y,2))) map_city
-> on map_node.id=map_city.mid
-> set map_node.city_id=map_city.cid;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from map_resource_node;
+------+------+------+---------+
| id | x | y | city_id |
+------+------+------+---------+
| 1 | 11 | 10 | 1 |
| 2 | 30 | 25 | 1 |
| 3 | 40 | 34 | 2 |
+------+------+------+---------+
3 rows in set (0.00 sec)