一个视图中有多个表?

问题描述：

今天我的问题是我将如何在使用两个以上表的MySQL数据库中创建视图?

Today my question is how would I go about creating a view in a MySQL database that uses more than two tables?

这是我的查询(有效)，我不是要更改当前查询，主要是希望找到一个很好的参考资料，并提供有关此主题的示例.

Here is my query (it works) I am not looking to change my current query, mostly looking for a nice reference with examples on this topic.

CREATE OR REPLACE VIEW vw_itemsPurchased AS
SELECT `tbl_buyers`.`fldPrimaryKey` as fldFKeyBuyer, `tbl_buyers`.`fldEmail` as fldBuyerEmail, `tbl_buyers`.`fldAddressStreet`, `tbl_buyers`.`fldAddressCity`, `tbl_buyers`.`fldAddressState`, `tbl_buyers`.`fldAddressZip`, `tbl_buyers`.`fldAddressCountry`, `fldPaymentCurrency`, `fldPaymentGross`, `fldPaymentStatus`, `fldReceiverEmail`, `fldTransactionId`
FROM `tbl_transactions` INNER JOIN `tbl_buyers`
ON `tbl_transactions`.`fldFKeyBuyer` = `tbl_buyers`.`fldPrimaryKey`

感谢您的时间！

答

要使用两个以上的表，只需继续添加JOIN语句以连接外键即可.修改您的代码以添加一个虚构的第三张表tbl_products可能看起来像这样:

To use more than two tables, you simply continue adding JOIN statements to connect foreign keys. Adapting your code to add an imaginary third table tbl_products might look like this:

CREATE OR REPLACE VIEW vw_itemsPurchased AS (
  SELECT 
   tbl_buyers.fldPrimaryKey as fldFKeyBuyer, 
   tbl_buyers.fldEmail as fldBuyerEmail, 
   tbl_buyers.fldAddressStreet, 
   tbl_buyers.fldAddressCity, 
   tbl_buyers.fldAddressState, 
   tbl_buyers.fldAddressZip, 
   tbl_buyers.fldAddressCountry, 
   fldPaymentCurrency, fldPaymentGross, 
   fldPaymentStatus, 
   fldReceiverEmail,
   fldTransactionId,
   tbl_tproducts.prodid
 FROM 
   tbl_transactions 
    INNER JOIN tbl_buyers ON tbl_transactions.fldFKeyBuyer = tbl_buyers.fldP
    -- Just add more JOINs like the first one..
    JOIN tbl_products ON tbl_products.prodid = tbl_transactions.prodid

在上述方法中，第一和第二表相关，而第一和第三表相关.如果必须关联table1->table2和table2->table3，请在FROM中列出多个表，然后在WHERE中进行关联.以下内容仅作说明之用，意义不大，因为您可能不会在与产品价格相同的表格中找到客户ID.

In the above method, the first and second tables relate, and the first and third tables relate. If you have to relate table1->table2 and table2->table3, list multiple tables in the FROM and relate them in the WHERE. The below is just for illustration and doesn't make much sense, as you probably wouldn't have a customer id in the same table as a product price.

SELECT
  t1.productid,
  t2.price,
  t3.custid
FROM t1, t2, t3
WHERE 
  -- Relationships are defined here...
  t1.productid = t2.productid 
  AND t2.custid = t3.custid

相关推荐