mysql - 如何在 MySql 中连接两个表时创建 View

Question

我需要创建一个名为 customer_addresses 的 View ，以显示每个客户的送货地址和账单地址。此 View 应返回 Customers 表中的这些列:

customer_id  
email_address  
last_name  
first_name.

这个 View 应该从 Addresses 表中返回这些列:

bill_line1  
bill_line2  
bill_city  
bill_state  
bill_zip  
ship_line1  
ship_line2  
ship_city  
ship_state  
ship_zip  

此 View 中的行应按 last_name 列排序，然后按 first_name 列排序。

注意事项
customers 表包括以下列:customer_id、email_address、password、first_name、last_name、shipping_address_id 和 billing_address_id

Addresses 表包括以下列:address_id、customer_id、line1、line2、city、state、zip_code、phone

我试图张贴两张 table 的照片，但我是新来的，还没有 10 个代表。由于将 billing_id/shipping_id 转换为实际地址，我主要在加入声明中遇到问题。

Answer 1

加入“地址”表两次，一次链接到账单地址 ID，另一次链接到送货地址 ID。

CREATE VIEW CUSTOMER_ADDRESSES AS 
SELECT cust.customer_id, cust.last_name, cust.first_name, cust.email_address
bill.line1 as bill_line1, bill.line2 as bill_line2, bill.city as bill_city, 
bill.state as bill_state, bill.zip as bill_zip, 
ship.line1 as ship_line1, ship.line2 as ship_line2, ship.city as ship_city, 
ship.state as ship_state, ship.zip as ship_zip 
FROM customers cust, addresses bill, addresses ship 
WHERE cust.shipping_address_id = ship.address_id 
and cust.billing_address_id = bill.address_id