MySQL创建连接两个表的视图 [英] MySQL create view joining two tables

问题描述

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

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

这篇关于MySQL创建连接两个表的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！