mysql - 多个数据库之间的 SQL 查询需要一些关联

Question

我在详细阐述 MySQL 查询时遇到另一个问题。

我有两个数据库，每个数据库都有一个包含城市的数据库。我的客户从另一家公司获得了他的旧网站，我需要获取旧数据库城市和新数据库城市之间的相关性。

这是数据库的结构:

数据库1

CREATE TABLE new_cities (
 new_id INT PRIMARY KEY,
 new_postal_code INT,
 new_city_name VARCHAR(255)
);

数据库2

CREATE TABLE old_cities (
 old_id INT PRIMARY KEY,
 old_postal_code INT,
 old_city_name VARCHAR(255)
);

CREATE TABLE old_entries (
entry_id INT PRIMARY KEY,
city_id INT
);

所以我必须将条目从一个数据库传输到另一个数据库，但问题是 old_entries.old_city_id 将与新数据库中的正确城市不对应，因为 ID 不同。

我想获取我的 old_entries.* 加上一个字段(或直接替换 old_city_id)，其中包含我通过邮政编码对应获得的 new_city_id。

这是我当前不起作用的查询:

SELECT *, 
(SELECT database1.new_cities.id FROM database1.new_cities, database2.old_entries WHERE  database1.new_cities.zipcode = 
(SELECT database2.old_cities.cp FROM database2.old_cities, database2.old_entries WHERE database2.old_entries.city_id = database2.old_cities.old_id)
) AS newcp FROM database2.old_entries WHERE 1

我收到一条逻辑消息:我的子查询返回不止一行。

结构应该是这样的:

1 - 获取我的 old_entry

2 - 获取old_entry.city_id并从old_cities表中获取相应的邮政编码

3 - 获取该邮政编码并获取与该邮政编码对应的new_cities.new_id

4 - 将这个new_id添加到AS newcp行中(或者直接用它替换city_id的值)

如何使查询获取每一行对应的 new_city_id AS newcp ？

编辑:示例

条目示例:

ID| CITY_ID
1 | 23000

旧城市样本

ID    | ZIPCODE | NAME
23000 |  75000  | Paris

新城市样本

ID    | ZIPCODE | NAME
23000 |  13000  | Marseille
23500 |  75000  | Paris

预期结果:

ID  |  OLD_CITY_ID  | NEW_CITY_ID
 1  |     23000     |   23500

或者最终

ID |  CITY_ID
 1 |   23500

所以我们可以看到，如果我转移的条目保留它的city_id，它就不再对应巴黎，而是对应马赛，所以我需要将entry.city_id中的23000替换为23500，这是该条目的新id巴黎市，由邮政编码 (75000) 标识

Answer 1

获取相应新城市 ID 的查询如下:

SELECT database2.old_entries.entry_id AS id, database1.new_cities.new_id AS city_id
FROM database2.old_entries AS entries 
    JOIN database2.old_cities AS old_cities ON (entries.city_id = old_cities.old_id)
    JOIN database1.new_cities AS new_cities ON (old_cities.old_postal_code = new_cities.new_postal_code)

这将返回与新城市 ID 匹配的所有条目 ID。另请参阅如何 JOIN有效。

如果要将结果插入到表中，则可以使用以下查询:

INSERT INTO new_entries (entry_id, city_id)
SELECT database2.old_entries.entry_id AS id, database1.new_cities.new_id AS city_id
FROM database2.old_entries AS entries 
    JOIN database2.old_cities AS old_cities ON (entries.city_id = old_cities.old_id)
    JOIN database1.new_cities AS new_cities ON (old_cities.old_postal_code = new_cities.new_postal_code)