MySQL 创建视图连接两个表

2021-12-29 00:00:00 join mysql view

如何创建将不同列与不同表合并的视图?例如我有三个表:用户、物品和礼物(在这个例子中是一个用户可以向另一个用户赠送礼物的系统)

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)

users 表包含有关用户的信息，items 表包含有关项目的信息，gifts 表显示哪个用户向哪个用户发送了什么礼物.

users table has information about users, items table has information about items and gifts table shows which user sent what gift to which user.

我想要的是创建一个如下所示的视图:

What I want is to create a view like following:

user_from | user_to | gift_name  | gift_price
sally     | john    | Teddy Bear | 10

推荐答案

必须先连接三个表.示例

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

相关文章