MySQL SQL - 从多个表创建视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13656864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Create view from multiple tables
提问by FidelCashflo
I have three tables:
我有三张表：
POP(country, year, pop)
FOOD(country, year, food)
INCOME(country, year, income)
I am trying to create a view such as:
我正在尝试创建一个视图，例如：
V(country, year, pop, food, income)
This is my code so far. I don't think its correct:
到目前为止，这是我的代码。我不认为它是正确的：
CREATE VIEW V AS
(SELECT * FROM POP
UNION
SELECT * FROM FOOD
UNION
SELECT * FROM INCOME);
One issue is, a country that is present in POPmay not be present in FOOD. Same goes for year. A year that is present in POPmay not be present in FOOD. So, I keep thinking that the above code will not work.
一个问题是，存在于 中的国家POP可能不存在于 中FOOD。一年也一样。存在于 中的年份POP可能不存在于 中FOOD。所以，我一直认为上面的代码行不通。
采纳答案by FidelCashflo
Thanks for the help. This is what I ended up doing in order to make it work.
谢谢您的帮助。这就是我最终为了让它工作而做的事情。
CREATE VIEW V AS
SELECT *
FROM ((POP NATURAL FULL OUTER JOIN FOOD)
NATURAL FULL OUTER JOIN INCOME);
回答by Ilion
Union is not what you want. You want to use joins to create single rows. It's a little unclear what constitutes a unique row in your tables and how they really relate to each other and it's also unclear if one table will have rows for every country in every year. But I think this will work:
联盟不是你想要的。您想使用连接来创建单行。有点不清楚表中的唯一行是由什么构成的，以及它们之间是如何真正关联的，而且还不清楚一个表是否每年都有针对每个国家/地区的行。但我认为这会奏效：
CREATE VIEW V AS (
SELECT i.country,i.year,p.pop,f.food,i.income FROM
INCOME i
LEFT JOIN
POP p
ON
i.country=p.country
LEFT JOIN
Food f
ON
i.country=f.country
WHERE
i.year=p.year
AND
i.year=f.year
);
The left (outer) join will return rows from the first table even if there are no matches in the second. I've written this assuming you would have a row for every country for every year in the income table. If you don't things get a bit hairy as MySQL does not have built in support for FULL OUTER JOINs last I checked. There are ways to simulate it, and they would involve unions. This article goes into some depth on the subject: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
即使第二个表中没有匹配项，左（外）连接也会从第一个表中返回行。我写这篇文章是假设你在收入表中每一年的每个国家都有一行。如果您不这样做，事情会变得有点麻烦，因为上次我检查过的 MySQL 没有内置对 FULL OUTER JOIN 的支持。有一些方法可以模拟它，它们将涉及工会。这篇文章深入探讨了这个主题：http: //www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
回答by cegfault
Are you using MySQLor PostgreSQL?
你在用MySQL还是PostgreSQL？
You want to use JOINsyntax, notUNION. For example, using INNER JOIN:
您想使用JOIN语法，而不是UNION。例如，使用 INNER JOIN：
CREATE VIEW V AS
SELECT POP.country, POP.year, POP.pop, FOOD.food, INCOME.income
FROM POP
INNER JOIN FOOD ON (POP.country=FOOD.country) AND (POP.year=FOOD.year)
INNER JOIN INCOME ON (POP.country=INCOME.country) AND (POP.year=INCOME.year)
However, this will only show results when each country and year are present in all three tables. If this is not what you want, look into left outer joins (using the same link above).
但是，这只会显示当每个国家和年份都出现在所有三个表中时的结果。如果这不是您想要的，请查看左外连接（使用上面相同的链接）。
回答by Emil Wallin
This works too and you dont have to use join or anything:
这也有效，您不必使用 join 或任何东西：
DROP VIEW IF EXISTS yourview;
CREATE VIEW yourview AS
SELECT table1.column1,
table2.column2
FROM
table1, table2
WHERE table1.column1 = table2.column1;
