MySQL 创建视图连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12352048/
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 tables
提问by Gokhan Sari
How can I create a view that merges different columns with a different table? I have three tables for example: users, items and gifts (in this example it's a system that a user can give a gift to another user)
如何创建将不同列与不同表合并的视图？例如，我有三个表：用户、物品和礼物（在这个例子中，它是一个用户可以向另一个用户赠送礼物的系统）
userstable has information about users, itemstable has information about items and giftstable shows which user sent what gift to which user.
users表格包含有关用户的信息，items表格包含有关物品的信息，gifts表格显示哪个用户向哪个用户发送了什么礼物。
What I want is to create a view like following:
我想要的是创建一个如下所示的视图：
user_from | user_to | gift_name | gift_price
sally | john | Teddy Bear | 10
回答by John Woo
You must join the three tables first. Example
您必须先连接三个表。例子
CREATE VIEW GiftsList
AS
SELECT b.name user_from,
c.name user_to,
d.name gift_name,
d.price gift_price
FROM gift a
INNER JOIN users b
ON a.user_from = b.id
INNER JOIN users c
ON a.user_from = c.id
INNER JOIN items d
ON a.item = d.id
回答by Dipanshu Mahla
You can create a view with two tables like:
您可以创建包含两个表的视图，例如：
CREATE VIEW giftList AS
SELECT users.user_from,users.user_to,gifts.gift_name,gifts.gift_price FROM users,gifts
WHERE users.user_id = gifts.user_id;
The where clause is to make sure the output does not repeat.
where 子句是为了确保输出不会重复。
