MySQL 创建视图连接两个整个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15187751/
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
MySQL create view joining two whole tables
提问by user2129160
How can I create a view that merges different all columns from two different tables.
如何创建合并来自两个不同表的不同所有列的视图。
CREATE VIEW listView
AS
SELECT * FROM tab1 h LEFT JOIN tab2 b
ON h.tID=b.tID
WHERE value = 0
this give me the error:
这给了我错误：
Duplicate column name 'tID'
重复的列名“tID”
Is there a way to join the two tables without listing all the values to select?
有没有办法在不列出所有要选择的值的情况下连接两个表？
采纳答案by John Woo
The two tables contains columns tID. In order to compile the VIEW, you need to create an alias on that column or just specify one tidand table where it will come from.
这两个表包含列tID。为了编译VIEW，您需要在该列上创建一个别名，或者只指定一个tid和它来自的表。
One solution:
一种解决方案：
SELECT h.TID, -- and not specifying b.TID
FROM tab1 h LEFT JOIN tab2 b ON h.tID=b.tID
Another solution: supply an alias,
另一种解决方案：提供别名，
SELECT h.TID as H_TID,
b.TID as B_TID
FROM tab1 h LEFT JOIN tab2 b ON h.tID=b.tID
回答by Anonymous
Try this:
尝试这个：
CREATE VIEW listView
AS
SELECT
a.tID as a_tID,
b.tID as b_tID,
a.anothercolumn as a_anothercolumn,
b.anothercolumn as b_anothercolumn
FROM tab1 a
JOIN tab2 b ON a.tID=b.tID
WHERE a.value = 0;
回答by sgeddes
You need to specify your column names instead of using * and then alias your columns like this:
您需要指定您的列名而不是使用 * 然后像这样为您的列设置别名：
SELECT h.tId, b.tId as BTId
You can't have the same name twice -- hence your error above.
你不能有两次相同的名字——因此你上面的错误。
