如何将连接结果放入新表中

Question

如何将此连接查询的结果放入同一模式上的新表中?

SELECT census2010.district,census2010.total,census2010.male, 
census2010.female,census2010.region, districts.area 
FROM ghana.census2010 LEFT JOIN ghana.districts 
ON census2010.region ILIKE districts.region

Answer 1

考虑上述代码，使用CREATE TABLE ___ AS ( )命令。这样的

CREATE TABLE ghana.TableName AS (
SELECT census2010.district,census2010.total,census2010.male,
census2010.female,census2010.region, districts.area  
FROM ghana.census2010 
LEFT JOIN ghana.districts  ON census2010.region ILIKE districts.region)

结果应该是相同数据库模式下的一个表

Answer 2

如果您希望在两个起始表中添加或删除新条目后连接表演变，则创建视图是合适的选择

CREATE VIEW view_name AS
SELECT census2010.district, census2010.total, census2010.male,
census2010.female, census2010.region, districts.area  
FROM ghana.census2010 
LEFT JOIN ghana.districts 
ON census2010.region ILIKE districts.region);

然后，您将能够运行如下查询

SELECT * FROM view_name;

，以获得始终最新的连接表。可以像删除其他表一样删除视图:

DROP VIEW view_name;