基于不同表中的值创建MySQL表

Question

如何根据另一个表中的值自动创建新表？例如，如果表A有一个名为city的列，其中包含不同的城市值，则需要基于每个不同的城市创建一个新表。然后，将所有具有相应城市的记录插入到其各自的表中。此外，如果城市名称中包含空格，则需要用下划线替换。如何在MySQL中完成相同的操作？

在MS ACCESS中，我可以通过以下方式实现：

1. Using A Select And Replace Query Named SELREP

   SELECT table_A.column1, table_A.column2, table_A.city, Replace([city]," ","_") AS table_name_column FROM table_A;
   

2. Create a Public Function MakeTableCity

   Public Function MakeTableCity()
   DoCmd.SetWarnings False
   Dim db As Database
   Set db = Application.CurrentDb
   Dim distinctValues As DAO.Recordset
   Set distinctValues = db.OpenRecordset("SELECT table_name_column FROM SELREP GROUP BY table_name_column", dbOpenSnapshot)
   Do Until distinctValues.EOF
   DoCmd.RunSQL "SELECT * INTO " & distinctValues("table_name_column") & " FROM SELREP WHERE table_name_column ='" & distinctValues("table_name_column") & "'"
   distinctValues.MoveNext
   Loop
   DoCmd.SetWarnings True
   Set distinctValues = Nothing
   Set db = Nothing
   End Function
   

- user739072

---

为什么需要为每个城市单独创建一张表？MySQL 可以处理将多个城市的数据放在同一个表中的情况。 - mellamokb

事实上，拥有多个结构相同的表格是糟糕设计的强烈迹象。这也是一种非规范化的形式。 - wallyk

因此，特定城市的用户可以轻松地导航到他们自己的城市数据。 - user739072

虽然你可以按城市进行分区，但很可能会出现相当不平衡的分区。（例如，俄勒冈州波特兰市的数据很可能比纽约市少） - Brian

只需使用 WHERE 子句按城市名称过滤结果。 - Phil Lello

Answer 1

• 如果您计划创建一个新的城市表，其中包含有关城市的数据，即每个城市一行，则可以继续阅读答案。

• 另一方面，如果您计划为每个城市创建一个具有相同列的新表，则您的计划是非常糟糕的设计。首先了解规范化。

---

第一种选择是创建一个名为city的表，其中包含您想要的字段。例如：

CREATE TABLE city
( id INT auto_increment PRIMARY KEY
, name VARCHAR(50) NOT NULL
, population INT
, state CHAR(2)
) ;

然后使用以下代码将不同的城市名称复制到其中：

INSERT INTO city (name)
  ( SELECT DINSTINCT city     --- change "city" into REPLACE(city, ' ', '_')
    FROM table_A              --- for the small changes you want 
  ) ; 

然后，更新其他字段（人口、州等）。

如果没有两个城市有相同的名称，则可以使用 ON table_A.city = city.name 进行两个表之间的 JOIN。

如果不是这样（并且更好的方式是因为 city 的 Primary Key 更小），您可以通过添加一个字段 cityid 并删除 city 字段来 ALTER table table_A 的结构。然后，两个表之间的 JOIN 将使用 ON table_A.cityid = city.id 进行。

---

第二个选项是直接使用以下语句创建城市表：

CREATE TABLE city AS
  ( SELECT DINSTINCT city AS name  --- change "city" into REPLACE(city, ' ', '_')
    FROM table_A                   --- for the small changes you want 
  ) ; 

然后修改表定义主键，添加（人口，州等）。

- ypercubeᵀᴹ